DAX: Find the consecutive negative number from a measure

[kc_1116]

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.

Day	Revenue	Cost	Profit (Measure)
1	100	90	10
2	50	60	-10
3	20	30	-10
4	30	10	20
5	40	60	-20
6	75	80	-5
7	10	15	-5
8	20	15	5

Power BI file:

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

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

[kc_1116]

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?

[OwenAuger]

@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.

[OwenAuger · Accepted solution]

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/