Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mp1977
Helper II
Helper II

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

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.


DateResultSum Consecutive Loss
02/01/2019 00:00240 
03/01/2019 00:000 
03/01/2019 00:0070 
03/01/2019 00:00200 
04/01/2019 00:00-240-240
07/01/2019 00:00-240-480
07/01/2019 00:000 
07/01/2019 00:00212 
08/01/2019 00:00-320-320
08/01/2019 00:00145 
08/01/2019 00:00160 
09/01/2019 00:00160 
09/01/2019 00:00350 
10/01/2019 00:0024 
10/01/2019 00:0040 
11/01/2019 00:00120 
11/01/2019 00:0070 
14/01/2019 00:00-240-240
15/01/2019 00:00-160-400
15/01/2019 00:000 
15/01/2019 00:00-40-40
16/01/2019 00:00-75-115
16/01/2019 00:00120 
16/01/2019 00:0080 
17/01/2019 00:00-10-10
17/01/2019 00:00-40-50
18/01/2019 00:00368 
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:00130 
22/01/2019 00:00102 
23/01/2019 00:00-200-200
24/01/2019 00:0070 
24/01/2019 00:00360 
28/01/2019 00:0080 
28/01/2019 00:00270 
29/01/2019 00:00220 
29/01/2019 00:00560 
30/01/2019 00:00-150-150
30/01/2019 00:0096 
31/01/2019 00:00200 
31/01/2019 00:00160 
01/02/2019 00:00180 
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:00110 
08/02/2019 00:0050 
08/02/2019 00:00-200-200
11/02/2019 00:00-240-440
11/02/2019 00:00-280-720
5 REPLIES 5
Anonymous
Not applicable

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.

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
Not applicable

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

Thank for your reply

Greg_Deckler
Community Champion
Community Champion

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.