The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
---|---|
24 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
9 | |
9 |