Forum Discussion
Helper IIIRolling months until the current Month
Hi Everyone,
I have a measure of sum of sales i.w SUM_SALES and I have a calendar Tablei.e Y which has Month_Year values and other Date column which is n other tablei.e X where the calendar table is defined from. So, I want the Rolling Months 12 months average of SUM_SALES until the max month of the Original Table X where I have used Mpnth_Year column values from the Y Table.
I have tried many other ways to find a solution but couldn't get it. Currently I am getting Rolling average values until december which is not the required, the required is like I need to see the Rolling avg values only until the July Month where the Maximum months exists in the X Table.
Can Anyone Help me with this ?
Thanks in advance
2 Replies
- amitchandak
Super User
kamalbandam , You can try something like this with a date table
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))Rolling 12 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-12,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. 
v-lionel-msftCommunity Support
Hi kamalbandam ,
Please provide sample data of table X and Y and expected output table.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
