Forum Discussion
Sum Previous Months (running total?)
Hello - I have tried using a standard cumulative value pattern...but it is not giving me the answer I need.
I have a date table. If I put the months on a matrix table, with the months going across as columns, using my example below, what I need is for Feb to show a total of 40....for March to show a total of 100, ...April to show a total of 121 etc....in other words....the month totals need to add up on each other. The cumulative value formula is showing "121" for every month.
I guess you could say I need a running total.
Jan Feb Mar Apr
10 30 60 21
5 Replies
- amitchandak
Super User
I hope you have a date calendar table. You can use YTD of overall cumulative formula, an example given below
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date]))) Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,endofmonth(dateadd(date[date]),-1,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/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on LinkedinI mentioned in my post that I do have a date table.
I found the solution here:
https://www.google.com/search?q=running+total+months+dax&ie=&oe=#kpvalbx=_KMJAXubGFMWa_Qa2652QBw31
Cumulative Demand =CALCULATE (SUM (Flu_PlanPegging[Outstanding Requirement]),FILTER (ALLSELECTED(Flu_PlanPegging),Flu_PlanPegging[Due Date] <= MAX ( Flu_PlanPegging[Due Date] )))- amitchandak
Anonymous
Is the issues resolved? Not clear with you last update.
