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.
Hi !
I want to calculate the min cumulative value for consecutive loss (negative value). This cumulative has to reset for every time a positive value happens.
My table is like this. And the result I need is: -1.616.
Date | Result | Sum Consecutive Loss |
02/01/2019 00:00 | 240 | |
03/01/2019 00:00 | 0 | |
03/01/2019 00:00 | 70 | |
03/01/2019 00:00 | 200 | |
04/01/2019 00:00 | -240 | -240 |
07/01/2019 00:00 | -240 | -480 |
07/01/2019 00:00 | 0 | |
07/01/2019 00:00 | 212 | |
08/01/2019 00:00 | -320 | -320 |
08/01/2019 00:00 | 145 | |
08/01/2019 00:00 | 160 | |
09/01/2019 00:00 | 160 | |
09/01/2019 00:00 | 350 | |
10/01/2019 00:00 | 24 | |
10/01/2019 00:00 | 40 | |
11/01/2019 00:00 | 120 | |
11/01/2019 00:00 | 70 | |
14/01/2019 00:00 | -240 | -240 |
15/01/2019 00:00 | -160 | -400 |
15/01/2019 00:00 | 0 | |
15/01/2019 00:00 | -40 | -40 |
16/01/2019 00:00 | -75 | -115 |
16/01/2019 00:00 | 120 | |
16/01/2019 00:00 | 80 | |
17/01/2019 00:00 | -10 | -10 |
17/01/2019 00:00 | -40 | -50 |
18/01/2019 00:00 | 368 | |
18/01/2019 00:00 | -40 | -40 |
21/01/2019 00:00 | -50 | -90 |
21/01/2019 00:00 | -120 | -210 |
22/01/2019 00:00 | 130 | |
22/01/2019 00:00 | 102 | |
23/01/2019 00:00 | -200 | -200 |
24/01/2019 00:00 | 70 | |
24/01/2019 00:00 | 360 | |
28/01/2019 00:00 | 80 | |
28/01/2019 00:00 | 270 | |
29/01/2019 00:00 | 220 | |
29/01/2019 00:00 | 560 | |
30/01/2019 00:00 | -150 | -150 |
30/01/2019 00:00 | 96 | |
31/01/2019 00:00 | 200 | |
31/01/2019 00:00 | 160 | |
01/02/2019 00:00 | 180 | |
01/02/2019 00:00 | -240 | -240 |
04/02/2019 00:00 | -240 | -480 |
04/02/2019 00:00 | -120 | -600 |
05/02/2019 00:00 | -320 | -920 |
05/02/2019 00:00 | -140 | -1060 |
06/02/2019 00:00 | -240 | -1300 |
07/02/2019 00:00 | -316 | -1616 |
07/02/2019 00:00 | 110 | |
08/02/2019 00:00 | 50 | |
08/02/2019 00:00 | -200 | -200 |
11/02/2019 00:00 | -240 | -440 |
11/02/2019 00:00 | -280 | -720 |
Hi !
Thank you for your reply, but the first row of my dataset is a date column. Can t I use it to compre the values?
M.P.
Thank for your reply
@Mp1977 - I think you would need an index on that table but the general approach would not change. If the current row value is >=0, return BLANK. Otherwise, Get the MAXX of the [Date] for all positive rows with a Date less than the current row's Date. Sum the values between > this value you just calculated and the current row's date. So, something along the lines of:
Sum Cumulative Loss Column =
IF([Result] >= 0,BLANK(),
SUMX(FILTER('Table',[Date] <= EARLIER([Date]) && [Date]>=MAXX(FILTER('Table',[Date] < EARLIER([Date]) && [Result] >= 0),[Date])),[Result])
)
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |