topic DAX: Find the consecutive negative number from a measure in DAX Commands and Tips

DAX: Find the consecutive negative number from a measure

kc_1116 — Sat, 15 Mar 2025 01:29:56 GMT

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_la6RgDV9w_A

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

Re: DAX: Find the consecutive negative number from a measure

OwenAuger — Sat, 15 Mar 2025 06:00:00 GMT

Hi kc_1116

The logic I would follow is:

For each Day d where Profit < 0 , find the nearest earlier Day d* where Profit >=0, and let Length = d - d*
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/

Re: DAX: Find the consecutive negative number from a measure

kc_1116 — Sun, 16 Mar 2025 12:14:49 GMT

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?

Re: DAX: Find the consecutive negative number from a measure

OwenAuger — Sun, 16 Mar 2025 22:00:10 GMT

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.