Forum Discussion

bosleyjarrett's avatar
bosleyjarrett
Frequent Visitor
9 years ago
Solved

Calculating Moving 90 Day sum and plotting on time graph

Hello,

 

I am trying to calculate a moving 90 day sum that I am then plotting on a graph with the actual months (think June has June, May & April data, May has March, April, and May data etc). My data set is cost data for each product for each month with a number of other measures. I have been attempting this through various versions of the "Calculate(,FILTER( or Calculate(,DatesBetween(" functions with no luck. Has anyone done this before? I also tried calculating a number of custom columns where I would sum the previous month and the month before and add the columns together but that did not work either.

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi bosleyjarrett

     

    This should be close and works for me.  This calculated measure assumes a relationship between the Sales and Dates tables.

     

     

    Measure = CALCULATE(
                    SUM('Sales'[Sales Qty]),
		    -------------------------------------------------------------------------
	            DATESINPERIOD('Dates'[Date],LASTDATE('Dates'[Date]),-90,DAY)
		  )

     

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi bosleyjarrett

     

    This should be close and works for me.  This calculated measure assumes a relationship between the Sales and Dates tables.

     

     

    Measure = CALCULATE(
                    SUM('Sales'[Sales Qty]),
		    -------------------------------------------------------------------------
	            DATESINPERIOD('Dates'[Date],LASTDATE('Dates'[Date]),-90,DAY)
		  )