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-01            A89764

2016-01-15            987643

2016-02-02            B98765

...

...

2016-12-31           F796435

 

And so on. What I'm trying to do is to count the quantity of W/O by month period then summarize that with a 3 month moving window. For example, we will sum the quantity of W/O from 2016-10-01 up to 2016-12-31. Then the next month, the value will go from 2016-11-01 up to 2017-01-31. I have searched the forum and tried lot of formula but nothing resolve my issues. Any ideas and help will be greatly appreciated.

 

Thank you! 

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" )
	)

10 Replies

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