Rolling Sum

[yodha]

Hi,

I want to calculate Rolling Sum for 3 months, starting form current month to past 2 months.

i have tried : 

Measure = CALCULATE([Count%],DATESINPERIOD('Date'[Date],LASTDATE('Table'[Date]),-3,MONTH))

Measure = CALCULATE([Count%],DATESINPERIOD('Date'[Date],MAX('Table'[Date]),-3,MONTH))

Measure = CALCULATE([Count%],DATESINPERIOD('Date'[Date],ENDOFTHEMONTH('Table'[Date]),-3,MONTH))

but none of these are working.

Any help on this much appreciated! Thank you.

[v-lionel-msft · Accepted solution]

Hi @yodha ,

1. Create a relationship between the two tables.

2. Create a measure like this.

Measure = 
CALCULATE(
    SUM('Table'[Count]),
    DATESINPERIOD( 'Date'[Date], MAX('Table'[Date]), -3, MONTH )
)

The return value of the current month is equal to the sum of the current month and the previous two months.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@yodha ,

Try like

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.