Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This is my data. I need the adjustedcumsum in DAX. I get the cumsum from sum. The cumum has to reset when value is negative and recalculate.
date | SUM | Cumsum | AdjustedSum |
2-1-2020 | -2 | -2 | 0 |
7-1-2020 | 2 | 0 | 2 |
8-1-2020 | -1 | -1 | 1 |
10-1-2020 | 4 | 3 | 5 |
13-1-2020 | -1 | 2 | 4 |
14-1-2020 | 1 | 3 | 5 |
15-1-2020 | 1 | 4 | 6 |
16-1-2020 | -1 | 3 | 5 |
17-1-2020 | 1 | 4 | 6 |
19-1-2020 | 3 | 7 | 9 |
20-1-2020 | -3 | 4 | 6 |
21-1-2020 | 0 | 4 | 6 |
22-1-2020 | 1 | 5 | 7 |
23-1-2020 | -1 | 4 | 6 |
24-1-2020 | 2 | 6 | 8 |
25-1-2020 | 1 | 7 | 9 |
27-1-2020 | 2 | 9 | 11 |
28-1-2020 | 1 | 10 | 12 |
29-1-2020 | 1 | 11 | 13 |
31-1-2020 | 2 | 13 | 15 |
4-2-2020 | -2 | 11 | 13 |
5-2-2020 | 2 | 13 | 15 |
6-2-2020 | 2 | 15 | 17 |
Hi @niveekiwi ,
Is "sum" a column or measure?
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sum is a measure.
Your result column AdjustedCumSum isn't showing the correct results, could you explain a bit more?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
So when my cumsum is negative (since sum is -2) in the first row is -2, the cumsum becomes 0. Then the rolling sum is recalculated again. So sum in the second row is 2, so then adjusted cumsum is 2. That is my new running sum. based on which the cumsum is calculated again. Hope that makes sense.
Why doesn't it reset with the -1 value? Please explain the needed logic to not reset there.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
When the cumsum is -1 on 08/01/2020; the new adjusted cumsum is 2 on 07/01/2020; and sum on 08/01/2020 is -1. So 2-1 is still 1 when the asjusted cumsum is considered.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |