Cumulative value for consecutive loss (negative value) resetting after a positive value

[Mp1977]

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

[Anonymous]

In DAX there is no notion of order. So, you have to have a column - date/time or integer - that will enable you to compare ANY two rows and tell which one precedes which one. Your data above does not have this property, so it's not possible to do what you want.

[Mp1977]

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.

[Anonymous]

No. Your date column is not unique, hence you can't.

[Mp1977]

Thank for your reply

[Greg_Deckler]

@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])
  )