Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
djbuncle
Helper I
Helper I

MIN calculation on cumulative value using FILTER

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

 

RD Model.png

 

CumulativeProfit := CALCULATE([Profit], FILTER(ALL('Days Since FTD'), 'Days Since FTD'[DaySeq] <= MAX('Days Since FTD'[DaySeq])))

 

RD Pivot.png

 

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.

1 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

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 😞

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Much appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.