locked
DAX date functions RRS feed

  • Question

  • Hi All,

            when I'm trying to find previousday sales and nextday day sales.. I'm getting error like below.

    Is it like we cannot do both function in the same sheet? 

    Thanks.


    • Edited by Murali dhar Tuesday, February 25, 2014 3:05 PM
    Monday, February 24, 2014 7:34 PM

Answers

  • Hi Murali,

    You are getting this issue due to the CALCULATE function converting the row context into a filter context (i.e. by combining the values in each column for a row). Because you already have at least one column that uses the CALCULATE function this is effectively causing the circular dependency. Take another look at the link I posted earlier for a more detailed explanation. One way to get around it in this specific scenario (since there is no unique identifier column present) is by ignoring the filter context that gets placed on the PREVIOUS_DAY_SALES calculated column. This blocks the CALCULATE function within this column from be 'triggered':

    =
    CALCULATE(
      SUM(Table1[SALES]), 
      ALL(Table1[PREVIOUS_DAY_SALES]),
      NEXTDAY(Table1[DATES])
    )


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)




    • Edited by Michael Amadi Tuesday, February 25, 2014 8:31 PM minor edit
    • Proposed as answer by Michael Amadi Tuesday, February 25, 2014 9:14 PM
    • Marked as answer by Murali dhar Tuesday, February 25, 2014 9:19 PM
    Tuesday, February 25, 2014 7:57 PM

All replies

  • Hi Murali,

    There is an article here that covers this area in some detail: http://www.sqlbi.com/articles/understanding-circular-dependencies. If you are able to provide the formulas behind your calculated columns, we may be able to advise on how you can resolve the issue in your scenario.


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Tuesday, February 25, 2014 12:47 PM
  • HI Michael,

      There are two columns in my sheet naming Dates,and sales respectively. Now I want to calculate 

    "=CALCULATE(SUM(Table1[SALES]),PREVIOUSDAY(Table1[DATES]))" in one column and 

    "=CALCULATE(SUM(Table1[SALES]),Nextday(Table1[DATES]))" in another column.. 

    Can't we do both in single sheet? As you provided the error I'm getting is something like "circular dependency" 

    Thanks.

    Tuesday, February 25, 2014 3:03 PM
  • Hi,

    You should probably use:

    =CALCULATE( SUM([SALES]), FILTER(Table1, NEXTDAY(Table1[DATES]) = EARLIER([DATES]) ) )
    =CALCULATE( SUM([SALES]), FILTER(Table1, PREVIOUSDAY(Table1[DATES]) = EARLIER([DATES]) ) )

    I believe your formulas would only work on tables marked as date tables.

    HTH,

    Hrvoje


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    • Marked as answer by Murali dhar Tuesday, February 25, 2014 9:19 PM
    • Unmarked as answer by Murali dhar Tuesday, February 25, 2014 9:19 PM
    Tuesday, February 25, 2014 7:24 PM
  • Hi,

    You should probably use:

    =CALCULATE( SUM([SALES]), FILTER(Table1, NEXTDAY(Table1[DATES]) = EARLIER([DATES]) ) )
    =CALCULATE( SUM([SALES]), FILTER(Table1, PREVIOUSDAY(Table1[DATES]) = EARLIER([DATES]) ) )

    I believe your formulas would only work on tables marked as date tables.

    HTH,

    Hrvoje


    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

    Thank you Sir. But why it wont work in my tables? why is it cause circular dependency? I have read about it and they mentioned something like identity column or primary key will help in avoiding such kind.. If so how can I use primary key and avoid such kind and use my formulas? 

    Thanks.

    Tuesday, February 25, 2014 7:32 PM
  • Hi Murali,

    You are getting this issue due to the CALCULATE function converting the row context into a filter context (i.e. by combining the values in each column for a row). Because you already have at least one column that uses the CALCULATE function this is effectively causing the circular dependency. Take another look at the link I posted earlier for a more detailed explanation. One way to get around it in this specific scenario (since there is no unique identifier column present) is by ignoring the filter context that gets placed on the PREVIOUS_DAY_SALES calculated column. This blocks the CALCULATE function within this column from be 'triggered':

    =
    CALCULATE(
      SUM(Table1[SALES]), 
      ALL(Table1[PREVIOUS_DAY_SALES]),
      NEXTDAY(Table1[DATES])
    )


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)




    • Edited by Michael Amadi Tuesday, February 25, 2014 8:31 PM minor edit
    • Proposed as answer by Michael Amadi Tuesday, February 25, 2014 9:14 PM
    • Marked as answer by Murali dhar Tuesday, February 25, 2014 9:19 PM
    Tuesday, February 25, 2014 7:57 PM
  • Thanks a lot Michael for detail explanation. I have gone through the link again. BTW, what is the difference between ALL and ALLEXCEPT?

    Also would you please suggest me what will be the best way to start learning DAX. I know there were lot of resources in internet. I'm following this 

    http://edu.abrahamesquivel.com/libros/libros/PowerPivot%20for%20the%20Data%20Analyst%20Microsoft%20Excel%202010.pdf

    Thanks.


    • Edited by Murali dhar Tuesday, February 25, 2014 8:36 PM
    Tuesday, February 25, 2014 8:34 PM
  • ALL will clear all of the filters placed on the table or columns specified whereas ALLEXCEPT will clear all the filters placed on a table except for the ones on the columns you specify.

    Normally you would use ALL when there are more columns with filters that you want to keep than the ones that you want to clear. ALLEXCEPT would generally be used when there are more columns with filters that you want to clear than the ones you want to keep.

    Here are the two official function reference pages:

    ALL: http://technet.microsoft.com/en-us/library/ee634802.aspx

    ALLEXCEPT: http://technet.microsoft.com/en-us/library/ee634795.aspx

    You could try following these tutorials to get a good grasp of the core stuff: http://www.microsoft.com/en-us/download/details.aspx?id=38838

    You can then explore this sample workbook to see how various functions and patterns have been used to solve some common problems: http://www.microsoft.com/en-us/download/details.aspx?id=38838 

    I highly recommend bookmarking the DAX function reference library to help you get an idea of what the various functions do: http://technet.microsoft.com/en-us/library/ee634396.aspx

    As always, the best way to learn will be by getting as much practical experience as possible :)


    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Tuesday, February 25, 2014 9:12 PM
  • Thanks Michael for your continuous support to my posts which boosting me up to learn :)

    Thanks.

    Tuesday, February 25, 2014 9:14 PM
  • No problem :)

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Tuesday, February 25, 2014 9:19 PM
  • Hi Michael,

                    Do you have chance to look at this post as well

    http://social.msdn.microsoft.com/Forums/en-US/62fbf56f-c2dc-41be-af50-aee17d34df1f/best-way?forum=sqlreportingservices

    Thanks.

    Wednesday, February 26, 2014 7:35 PM