Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I was wondering whether someone can help me with the behaviour of this calculation.
I'm trying to get the number of days (DaySeq) it takes for the cumulative value (CumulativeProfit) to reach 0 where it starts from a negative value. The model I have here is...
CumulativeProfit := CALCULATE([Profit], FILTER(ALL('Days Since FTD'), 'Days Since FTD'[DaySeq] <= MAX('Days Since FTD'[DaySeq])))
In the pivot data here we see that player 1 reached profit of >=0 after 2 days and player 2 in 7 days (in yellow and green respectively). However my [Repayment Days] calculation returns 1 for both players. Here's the formula I'm using:
Repayment Days:= CALCULATE(MIN('Days Since FTD'[DaySeq]), FILTER('PlayerRev',[CumulativeProfit] >= 0))
I have a hunch that the cumulative value on which this is based is putting a spanner in the works. Any suggestions would be much appreciated.
Cheers.
Solved! Go to Solution.
Here are a few ways of writing the measure that I can think of:
Repayment Days 1 := CALCULATE ( MIN ( 'Days Since FTD'[DaySeq] ), FILTER ( 'Days Since FTD', [Cumulative Profit] >= 0 ) )
Repayment Days 2 := FIRSTNONBLANK ( 'Days Since FTD'[DaySeq], IF ( [Cumulative Profit] >= 0, 1 ) )
Repayment Days 3 := MINX ( 'Days Since FTD', IF ( [Cumulative Profit] >= 0, 'Days Since FTD'[DaySeq] ) )
Regards,
Owen
Hi djbuncle,
Modify your first measure as below and check if it can work:
CumulativeProfit = CALCULATE ( SUM ( [Profit] ), FILTER ( ALL ( 'Days Since FTD' ), 'Days Since FTD'[DaySeq] <= MAX ( 'Days Since FTD'[DaySeq] ) ) )
Regards,
Jimmy Tao
Thanks Jimmy, but the [Profit] measure I have in the cumulative calculation is already a SUM([ProfitMeasure]) so that won't be the issue, I tried it and it made no difference 😞
Here are a few ways of writing the measure that I can think of:
Repayment Days 1 := CALCULATE ( MIN ( 'Days Since FTD'[DaySeq] ), FILTER ( 'Days Since FTD', [Cumulative Profit] >= 0 ) )
Repayment Days 2 := FIRSTNONBLANK ( 'Days Since FTD'[DaySeq], IF ( [Cumulative Profit] >= 0, 1 ) )
Repayment Days 3 := MINX ( 'Days Since FTD', IF ( [Cumulative Profit] >= 0, 'Days Since FTD'[DaySeq] ) )
Regards,
Owen
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |