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
kc_1116
Frequent Visitor

DAX: Find the consecutive negative number from a measure

Hi,

 

I would like to find the number of consecutive days when the profit (a measure) is a negative number. In the sample data below, the expect result should be 3.

 

I made an auxilery measure called Loss Making,

Loss Making = 1 * ([Profit] < 0)

 

I am looking to do a running sum of the above measure to calculate the number consecutive loss days, but not sure how to setup the calculate/filter properly.

 

DayRevenueCostProfit (Measure)
11009010
25060-10
32030-10
43010

20

54060-20
67580-5
71015-5
820155

 

Power BI file:

https://limewire.com/d/2b2c0008-b44c-4395-9a21-352a609b8d33#-7GklRjbHQmOPyhxfFBw6bBLd3qlNcq_la6RgDV9...

 

Can you shed me some light please? Thanks in advance.

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @kc_1116 

The logic I would follow is:

  1. For each Day d where Profit < 0 , find the nearest earlier Day d* where Profit >=0, and let Length = d - d*
  2. Return the maximum value of Length calculated for each day above.

I would suggest creating a Day or Date dimension separate from the fact table.

In any case, here are a few ways to write this with your current model:

Max Consecutive Loss Days v3 =
VAR DaysWithProfit =
    FILTER ( VALUES ( 'Table'[Day] ), [Profit] >= 0 )
VAR MaxDayWithProfit =
    MAXX ( DaysWithProfit, 'Table'[Day] )
VAR MinDayMinus1 =
    MIN ( 'Table'[Day] ) - 1
VAR MaxDayPlus1 =
    MAX ( 'Table'[Day] ) + 1
VAR MaxLossLength =
    MAXX (
        DaysWithProfit,
        VAR PreviousDayWithProfit =
            COALESCE (
                OFFSET ( -1, DaysWithProfit, ORDERBY ( 'Table'[Day], ASC ) ),
                MinDayMinus1
            )
        RETURN
            'Table'[Day] - PreviousDayWithProfit - 1
    )
VAR MaxLossLengthAdj =
    MAX ( MaxLossLength, MaxDayPlus1 - MaxDayWithProfit - 1 )
RETURN
    MaxLossLengthAdj

 

Max Consecutive Loss Days v1 = 
VAR VisibleDays = VALUES ( 'Table'[Day] )
VAR MinDayMinus1 = MIN ( 'Table'[Day] ) - 1
VAR DaysWithLoss = FILTER ( VisibleDays, [Profit] < 0 )
VAR DaysWithProfit = EXCEPT ( VisibleDays, DaysWithLoss )
VAR Result =
    MAXX (
        DaysWithLoss,
        VAR CurrentDay = 'Table'[Day]
        VAR NearestProfitDay =
            CALCULATE (
                MAX ( 'Table'[Day] ),
                'Table'[Day] < CurrentDay,
                DaysWithProfit
            )
        -- Handle case when LastProfitDay is BLANK
        VAR NearestProfitDayAdj = COALESCE ( NearestProfitDay, MinDayMinus1 )
        RETURN
            CurrentDay - NearestProfitDayAdj
    )
RETURN
    Result

 

 

Max Consecutive Loss Days v2 = 
VAR VisibleDays = VALUES ( 'Table'[Day] )
VAR MinDayMinus1 = MIN ( 'Table'[Day] ) - 1
VAR Result =
    MAXX (
        VALUES ( 'Table'[Day] ),
        VAR CurrentDay = 'Table'[Day]
        VAR NearestProfitDay =
            CALCULATE (
                LASTNONBLANK ( 'Table'[Day], IF ( [Profit] >= 0, 1 ) ),
                'Table'[Day] <= CurrentDay,
                VisibleDays
            )
        -- Handle case when LastProfitDay is BLANK
        VAR NearestProfitDayAdj = COALESCE ( NearestProfitDay, MinDayMinus1 )
        RETURN
            CurrentDay - NearestProfitDayAdj
    )
RETURN
    Result

 

Take a look here for a solution to a similar problem:

https://www.sqlbi.com/tv/counting-consecutive-days-with-sales-unplugged-47/


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

3 REPLIES 3
kc_1116
Frequent Visitor

Thanks for your help as always.

Btw, I believe understanding how filter context works is probably more important than DAX itself. Do you have any recommendation what books or other resources a beginner should read?

@kc_1116 you're welcome!

I agree, filter context and other core concepts are definitely important!

The SQLBI DAX 101 series is a great resource, starting with Filter Context in DAX.

Otherwise, books like Supercharge Power BI by Matt Allington.

For a more advanced treatment, I would recommend The Definitive Guide to DAX.

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn
OwenAuger
Super User
Super User

Hi @kc_1116 

The logic I would follow is:

  1. For each Day d where Profit < 0 , find the nearest earlier Day d* where Profit >=0, and let Length = d - d*
  2. Return the maximum value of Length calculated for each day above.

I would suggest creating a Day or Date dimension separate from the fact table.

In any case, here are a few ways to write this with your current model:

Max Consecutive Loss Days v3 =
VAR DaysWithProfit =
    FILTER ( VALUES ( 'Table'[Day] ), [Profit] >= 0 )
VAR MaxDayWithProfit =
    MAXX ( DaysWithProfit, 'Table'[Day] )
VAR MinDayMinus1 =
    MIN ( 'Table'[Day] ) - 1
VAR MaxDayPlus1 =
    MAX ( 'Table'[Day] ) + 1
VAR MaxLossLength =
    MAXX (
        DaysWithProfit,
        VAR PreviousDayWithProfit =
            COALESCE (
                OFFSET ( -1, DaysWithProfit, ORDERBY ( 'Table'[Day], ASC ) ),
                MinDayMinus1
            )
        RETURN
            'Table'[Day] - PreviousDayWithProfit - 1
    )
VAR MaxLossLengthAdj =
    MAX ( MaxLossLength, MaxDayPlus1 - MaxDayWithProfit - 1 )
RETURN
    MaxLossLengthAdj

 

Max Consecutive Loss Days v1 = 
VAR VisibleDays = VALUES ( 'Table'[Day] )
VAR MinDayMinus1 = MIN ( 'Table'[Day] ) - 1
VAR DaysWithLoss = FILTER ( VisibleDays, [Profit] < 0 )
VAR DaysWithProfit = EXCEPT ( VisibleDays, DaysWithLoss )
VAR Result =
    MAXX (
        DaysWithLoss,
        VAR CurrentDay = 'Table'[Day]
        VAR NearestProfitDay =
            CALCULATE (
                MAX ( 'Table'[Day] ),
                'Table'[Day] < CurrentDay,
                DaysWithProfit
            )
        -- Handle case when LastProfitDay is BLANK
        VAR NearestProfitDayAdj = COALESCE ( NearestProfitDay, MinDayMinus1 )
        RETURN
            CurrentDay - NearestProfitDayAdj
    )
RETURN
    Result

 

 

Max Consecutive Loss Days v2 = 
VAR VisibleDays = VALUES ( 'Table'[Day] )
VAR MinDayMinus1 = MIN ( 'Table'[Day] ) - 1
VAR Result =
    MAXX (
        VALUES ( 'Table'[Day] ),
        VAR CurrentDay = 'Table'[Day]
        VAR NearestProfitDay =
            CALCULATE (
                LASTNONBLANK ( 'Table'[Day], IF ( [Profit] >= 0, 1 ) ),
                'Table'[Day] <= CurrentDay,
                VisibleDays
            )
        -- Handle case when LastProfitDay is BLANK
        VAR NearestProfitDayAdj = COALESCE ( NearestProfitDay, MinDayMinus1 )
        RETURN
            CurrentDay - NearestProfitDayAdj
    )
RETURN
    Result

 

Take a look here for a solution to a similar problem:

https://www.sqlbi.com/tv/counting-consecutive-days-with-sales-unplugged-47/


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

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.