Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DateADD function does not work for Q1, maybe due to a filter?

Hi!

 

As I was doing an exercise I faced a problem. The exercise was to add another measure which shows for each time period in 2015 what the sales were in the previous quarter, using the CALCULATE and DATEADD function.

 

So the new measure I made was:

Sales Previous Quarter = CALCULATE([Total Quanitity];DATEADD(tblCalendar[DateKey].[Date];-1;QUARTER))
 
This works perfectly for Q2, Q3 and Q4 (all 2015). But Q1 gives me data from Q4 2015 in stead of Q4 2014.
 
I think that the filter that I have applied to my matrix (filter = 2015 only) gives me this problem, but there must be a way to still get the previous quarter data from Q1 aswell.
 
Can somebody please explain what I should do?
 
Thank you!
 
 
Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous ,

    It seems that you have problem when create measure with Calculate () and DATEADD().

    Please refer to my test pbix.

    In my example, I have the sales data from 2018-2019 and a calendar table.

    I have created the relationship between the two tables like below.

    Then create the measure like this.

    previous quar =
CALCULATE ( SUM ( 'Sheet5'[Value] ), DATEADD ( 'calendar'[Date], -1, QUARTER ) )

    Here is my test output.

    If you still need help, please share your data sample and your desired output so that we could help further on it.

    Best  Regards,

    Cherry

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-piga-msft 

       

      Thank you for your reply. Unfortunately this is not the solution that works for me as I already created the relationship between the two tables. 

       

      Is it possible for me to share the PBI file here? This way you  can take a look yourself