Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Previous Quarters SUM

Hi folks,

 

I have managed to create a measure to calculate the last quarter invoice amount see below and all works well:

 

Last Quarter Average = CALCULATE('Measures Store'[Invoice Spend],DATESINPERIOD('D&A and Shared Tech spend repor'[Accounting Date],MAX('D&A and Shared Tech spend repor'[Accounting Date]),-1,QUARTER))
 
I am now trying to write a measure to calculate the sum of the previous four quarters (not including the above quarter i have calculated so that i can compare the two) - any suggestions on how best to do this?

Thanks
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous ,

     

    Rolling 4 qtr = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-4,Quarter))

     

    Rolling 4 qtr before 1=

    var _max1 = MAX( 'Date'[Date])
    var _max = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,Month(_max1)))

    return

    CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_max,-4,Quarter))

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous ,

     

    Rolling 4 qtr = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-4,Quarter))

     

    Rolling 4 qtr before 1=

    var _max1 = MAX( 'Date'[Date])
    var _max = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,Month(_max1)))

    return

    CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_max,-4,Quarter))