locked
Trailing 4 quarters DAX calculation on an aggregated level RRS feed

  • Question

  • Hi,

    I am having difficulties with the calculation of the last 4 quarters in DAX. I am using a standard fact table and a separate time table (Time). The calculation works fine on the quarterly level but I can't find a way to display the correct results on a total year level.

    My calculation is as follows:

    Dividends TTM:=CALCULATE([Dividend], 
    DATESBETWEEN(Time[Date],
    DATEADD(FIRSTDATE(time[date]),-3, QUARTER),
    LASTDATE(time[date])))

    Thanks a lot for your help!

    Tuesday, January 14, 2014 11:17 PM

Answers

  • I proposed using -360 days knowing that data is only stored on a monthly level and always on the last of the month, so -360 is "OK" to use here but yes, its more of a dirty solution

    after reconsidering the calculation this is probably the most correct way to calculate it: 

    Dividends TTM:=CALCULATE(
    [Dividend], 
    DATESINPERIOD(Time[Date], MAX(Time[Date]), -4, QUARTER)
    )
    

    -gerhard


    - www.pmOne.com -

    Friday, January 17, 2014 11:23 AM
    Answerer
  • Something like the following logic might work at both the year level and below

    Dividends TTM:=CALCULATE([Dividend], 
    DATESBETWEEN(Time[Date],
    DATEADD(LASTDATE(time[date]),-4, QUARTER),
    LASTDATE(time[date])))


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Karaoan Wednesday, January 15, 2014 1:16 AM
    Wednesday, January 15, 2014 12:42 AM
  • "Quarter" is a parameter that specifies the time interval to use for the DATESINPERIOD function. More at: https://msdn.microsoft.com/en-us/library/ee634539.aspx?f=255&MSPPError=-2147217396.

    Martin

    http://powerbiexperts.com.au

    • Marked as answer by Karaoan Thursday, March 2, 2017 6:43 AM
    Thursday, March 2, 2017 6:42 AM

All replies

  • Something like the following logic might work at both the year level and below

    Dividends TTM:=CALCULATE([Dividend], 
    DATESBETWEEN(Time[Date],
    DATEADD(LASTDATE(time[date]),-4, QUARTER),
    LASTDATE(time[date])))


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Karaoan Wednesday, January 15, 2014 1:16 AM
    Wednesday, January 15, 2014 12:42 AM
  • Thanks a lot for your quick help! That has worked.
    Wednesday, January 15, 2014 1:16 AM
  • Hi Darren, On a closer look I still have problems. With your calculation I get now 5 quarters added up although the relevant dates are not within 4 quarters (31/3, 30/6, 30/9 and 31/12 each year):

    Wednesday, January 15, 2014 2:36 PM
  • So I think it's actually returning 4 Quarters and 1 day which you could fix with something like the following:

    Dividends TTM:=CALCULATE([Dividend], 
    DATESBETWEEN(Time[Date],
    DATEADD(DATEADD(LASTDATE(time[date]),-4, QUARTER),1,DAY) ,
    LASTDATE(time[date])))


    http://darren.gosbell.com - please mark correct answers

    Wednesday, January 15, 2014 7:44 PM
  • Thanks Darren! That has worked. In the meantime we also had another solution that was given by Gerhard Brueckl which also fixed the issue: 

    Dividends TTM:=CALCULATE([Dividend], 

    DATESBETWEEN(Time[Date],
    DATEADD(LASTDATE(time[date]),-360, DAY),
    LASTDATE(time[date])))

    Is in your opionion this 4 quarter +1 day behaviour by design or a bug?

    Thursday, January 16, 2014 1:48 PM
  • Is in your opionion this 4 quarter +1 day behaviour by design or a bug?

    It's definitely by design. The problem was with my logic, the function was returning exactly what it should. 

    It's just one of the pitfalls of suggesting expressions without having access to the data model to be able to verify the calculation.

    The only potential issue that may exist with Gerhard's approach is that it's actually a rolling 360 days, so i you have any data in the first 5 days of the first quarter it will be excluded. I also don't know if you are worried about how the calculations will work at grains lower than quarter (ie. at the Month and Date levels) - as I think they may work differently


    http://darren.gosbell.com - please mark correct answers

    Friday, January 17, 2014 1:04 AM
  • I proposed using -360 days knowing that data is only stored on a monthly level and always on the last of the month, so -360 is "OK" to use here but yes, its more of a dirty solution

    after reconsidering the calculation this is probably the most correct way to calculate it: 

    Dividends TTM:=CALCULATE(
    [Dividend], 
    DATESINPERIOD(Time[Date], MAX(Time[Date]), -4, QUARTER)
    )
    

    -gerhard


    - www.pmOne.com -

    Friday, January 17, 2014 11:23 AM
    Answerer
  • @Gerhard - Good work! I like your last expression the best. I think it should work in most circumstances and is better than both our first attempts. :)

    http://darren.gosbell.com - please mark correct answers

    Saturday, January 18, 2014 9:28 PM
  • Thank you for this expression. I was having troubles trying to figure out TTM expression in DAX. My only question is what does the "quarter" stand for? I don't see that popping up in any expression or filter when typing expressions or filters. 
    Thursday, March 2, 2017 6:17 AM
  • "Quarter" is a parameter that specifies the time interval to use for the DATESINPERIOD function. More at: https://msdn.microsoft.com/en-us/library/ee634539.aspx?f=255&MSPPError=-2147217396.

    Martin

    http://powerbiexperts.com.au

    • Marked as answer by Karaoan Thursday, March 2, 2017 6:43 AM
    Thursday, March 2, 2017 6:42 AM