Forum Discussion

David_C's avatar
David_C
Regular Visitor
9 years ago
Solved

Rolling Sum Calculation

Hello to all,   I'm stuck with a simple task from a while now. I have a table that contains fields as follow:   Date:                     W/O id:             2016-01-01            F7895 2016-01...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    I think this might be close to what you need?

     

    3 Month RT MEASURE = CALCULATE (
    COUNTROWS ( 
    	DATESINPERIOD (
        	'Table'[Date],
			LASTDATE('Calendar'[Date]),-3,MONTH)
			),
			FILTER (  'Calendar', 'Calendar'[Full Month] = "Full Month" )
	)
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Sean did the hard yards.  I just tweaked it for the cumulative requirement.

Sean's avatar
Sean
Community Champion
9 years ago

1) Create a COLUMN in your Calendar Table

 

Full Month =
IF (
    TODAY () >= EOMONTH ( 'Calendar'[Date], 0 ),
    "Full Month",
    "Incomplete Month"
)

2) And then the 3 Month RT MEASURE

 

3 Month RT MEASURE = 
CALCULATE (
    COUNTROWS ( Table ),
    DATESINPERIOD (
        Table[Date],
        CALCULATE (
            LASTDATE ( 'Calendar'[Date] ),
            FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Full Month] = "Full Month" )
        ),
        -3,
        MONTH
    )
)

As I posted yesterday DATESINPERIOD works even without a Calendar Table

as you see in formula above the Table[Date] column - the 1st argument is your Table not the Calendar

we only use the Calendar to get the last date of the last full month

 

Hope this helps! :smileyhappy:

David_C's avatar
David_C
Regular Visitor
9 years ago

Ok I will give a try. I have already tested with DatesInperiod function in the past but not exactly as you did. I will let you know.

Thank you!

  • David_C's avatar
    David_C
    Regular Visitor
    9 years ago

    Ok I did test the formula you provided me. However, I still get a monthly value while I want a cumulated value. 

    Ex.: September 2016 will hold the value that sum all the WO from a 3 month period , Jully 2016 up to September 2016 and so on up January 2017 that will hold value of November 2016 up to January 2017. Each month must display the sum of last 3 month period. I hope I'm precise enough. Thanks for your help!

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      I think this might be close to what you need?

       

      3 Month RT MEASURE = CALCULATE (
    COUNTROWS ( 
    	DATESINPERIOD (
        	'Table'[Date],
			LASTDATE('Calendar'[Date]),-3,MONTH)
			),
			FILTER (  'Calendar', 'Calendar'[Full Month] = "Full Month" )
	)
      • David_C's avatar
        David_C
        Regular Visitor
        9 years ago

        Thank you Guys! With all your help and the final solution from Phil it works great, exactly what I need.

        Have a good day!