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!