Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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:
Can you shed me some light please? Thanks in advance.
Solved! Go to Solution.
Hi @kc_1116
The logic I would follow is:
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/
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.
Hi @kc_1116
The logic I would follow is:
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/
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
17 | |
10 | |
9 | |
8 | |
7 |
User | Count |
---|---|
20 | |
11 | |
8 | |
6 | |
6 |