Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
Following my previous post - https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-Multiple-Months-Sort-Issue/m-p...
Could I please have some advise on how to perform cumulative sum and percentages for each month without using the rank dax?
So now the data is displaying correctly by month order but the sum for May is incorrect. It should be 16 + 2 (June) = 18; then 18 + 3 (July) = 21. However, because July's figure is larger than June, it had ranked it 2 instead of 3. I can forsee this being an issue in the future when the dataset gets larger. Therefore, I would need a cumulative sum method that is not based on rank.
Additionally, in the percentage column, is it possible to have the cumulative % for each month and then the remainder % in the blank (month). At the moment, it is copying the same % as the last month. For example April's blank cell should have 100% - 66.10% = 33.9%.
Is this possible please? I hope it makes sense. Thank you so much for any help and advise in advance.
Solved! Go to Solution.
I have found the solution for this issue.
The final formula is
Try this DAX:
Cumm Based on Date = CALCULATE(Sum(Table[Booking filled]) , Window(1,ABS,0,REL, Summarize(ALLSELECTED('Table'), Table[Vacancy Month Year], Table[Date Filled Month Year], Table[Date Filled Month Year Sort]),ORDERBY('Table'[Date Filled Month Year Sort],ASC), PARTITIONBY(Table[Vacancy Month Year]) ))
For information refer to this video/blog:
Continue 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-3d98a5b0e07f
For the formula below, I don't have a column for 'Table'[Date Filled Month Year Sort], what would this entail please?
Cumm Based on Date = CALCULATE(Sum(Table[Booking filled]) , Window(1,ABS,0,REL, Summarize(ALLSELECTED('Table'), Table[Vacancy Month Year], Table[Date Filled Month Year], Table[Date Filled Month Year Sort]),ORDERBY('Table'[Date Filled Month Year Sort],ASC), PARTITIONBY(Table[Vacancy Month Year]) ))
I also tried using Example 2 from here - https://learn.microsoft.com/en-us/dax/window-function-dax but it didn't work with the PARTITIONBY.
I got this working instead but it is not doing a cumulative as shown in the example, possible due to the PARTITIONBY issue.
I have found the solution for this issue.
The final formula is
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |