MIN calculation on cumulative value using FILTER

[djbuncle]

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.

[v-yuta-msft]

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

[djbuncle]

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 😞

[OwenAuger · Accepted solution]

@djbuncle

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

[djbuncle]

Absolute champion, @OwenAuger. All 3 of those worked.

Much appreciated.