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

Do you have a Calendar Table?

And

You only want to count full/completed months right?

David_C's avatar
David_C
Regular Visitor
9 years ago

I do have a calendar table however I would prefer to use the date that already exist in my table. And yes I want full/completed months calculation.

Thank you!

  • 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!