Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have created a running total using a calendar table and IDs using this formula:
Cumulative count =
CALCULATE(
COUNTA('Table'[IDs]),
FILTER(ALLSELECTED('Calendar table'[Date]),
ISONORAFTER('Calendar table'[Date],MAX('Calendar table'[Date]),DESC)))
I have the first two columns in the table below, is there a way to get the third column by computing a cumulative count of my initial cumulative count? The third column is needed for an availability calculation I am doing at week level rather than day. I have tried nesting and summing but because it is a measure I cannot sum. Any help would be greatly appreciated.
Date | Cumulative count | Cumulative count of cumulative count |
15/06/20 | 10 | 10 |
16/06/20 | 20 | 30 |
17/06/20 | 30 | 60 |
18/06/20 | 40 | 100 |
19/06/20 | 50 | 150 |
Solved! Go to Solution.
Please try this expression in a new measure.
Cumulative count X2 =
CALCULATE(
SUMX('Calendar table', [Cumulative count]),
FILTER(ALLSELECTED('Calendar table'[Date]),
ISONORAFTER('Calendar table'[Date],MAX('Calendar table'[Date]),DESC)))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this expression in a new measure.
Cumulative count X2 =
CALCULATE(
SUMX('Calendar table', [Cumulative count]),
FILTER(ALLSELECTED('Calendar table'[Date]),
ISONORAFTER('Calendar table'[Date],MAX('Calendar table'[Date]),DESC)))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you so much, this did exactly what I asked for.
User | Count |
---|---|
57 | |
21 | |
19 | |
17 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |