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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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