Forum Discussion
Post PatronCumulative Rolling Sum and Quotient
Hi everybody,
I'm creating a DAX for monthly rolling quotient per month. Wherein (Sum of Target / Count Sum).
Target table looks like this. Only months is used since these are the same target numbers that will be used in the succeeding years:
This is the table where the data of "Count Sum" looks like:
When summarized into a table this is how it will look like. "Count Sum" is the sum of the "Count" column in the above-mentioned table:
Now I want to add a new column which is "On Track %" column wherein, "Count Sum" per month divided by the "Target" per month to be a cumulative rolling quotient by month. Below is how the finished output will look like:
Just for reference, here is how the formula looks like in Excel:
Here is the link containing the pbix sample data for reference:
https://www.dropbox.com/scl/fi/qq1vt0s1mjsar49izflwn/Sample-November-21-2023.pbix?rlkey=1t0o9nmb628kw0ucwd83xfagw&dl=0
Appreciate if you can help me in creating DAX for the above. Thanks.
Best regards,
Mark V
1 Reply
- amitchandak
Super User
markefrody , I am assuming you can have year in Target table or plan to join it with date table on Month
Join both tables to a common date table and have measures like
Cumm Sales = CALCULATE(Divide( SUM(Sales[Sales]), SUM(Target[Target]) ) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm Sales = CALCULATE(Divide( SUM(Sales[Sales]), SUM(Target[Target]) ) ,filter(allselected(date),date[date] <=max(date[Date])))
Cumm Based on Date = CALCULATE(Divide( SUM(Sales[Sales]), SUM(Target[Target]) ) , Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm Based on Date = CALCULATE(Divide( SUM(Sales[Sales]), SUM(Target[Target]) ) , Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))
Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07fIn case you need YTD
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
