Forum Discussion
Post PatronRunning Total for Rolling 12
Hello All,
I'm having some trouble getting my Running Total for Rolling 12 to show up correctly in my table.
I'm trying to get the running total for Open Tickets. It shows the total running all the way through though which isn't correct. I would expect May 2019 thru Oct 2019 to be blank, then November 2019 thru January 2020 = 1, Feb 2020 thru Mar 2020 = 3, and Apr 2020 = 16
Here is the formula I'm using to calculate Running Total:
Hi, nleuck
Based on your description, I assume that you want to calculate cumulative value. I created data to reproduce your scenario. The pbix file is attached in the end.
You may create a measure as below.
Result = var _lastdate = LASTDATE(Sales[Date]) return CALCULATE( SUM(Sales[Total Open Tickets]), FILTER( ALL(Sales), Sales[Date]<=_lastdate ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- parry2k
Super User
nleuck check these posts
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
https://community.powerbi.com/t5/Desktop/Trailing-12-or-Rolling-12-month-sum/td-p/164419
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
- amitchandak
nleuck , do you have start date and end date then refer this
If there is a single date, we can use rolling formula
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-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.
nleuckI do have a date calendar and it's has a relationship to my data. Here are the results I get when I try to use those two formulas:
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))
v-alq-msftCommunity Support
Hi, nleuck
Based on your description, I assume that you want to calculate cumulative value. I created data to reproduce your scenario. The pbix file is attached in the end.
You may create a measure as below.
Result = var _lastdate = LASTDATE(Sales[Date]) return CALCULATE( SUM(Sales[Total Open Tickets]), FILTER( ALL(Sales), Sales[Date]<=_lastdate ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
