Forum Discussion
Rolling Sum
Hi,
I want to calculate Rolling Sum for 3 months, starting form current month to past 2 months.
i have tried :
Any help on this much appreciated! Thank you.
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
