Stop Horizontal line in visual of a running total, when max value is reached

[JohannesE]

Hello All, 

I have got a question and de "googled solutions didn't work for me yet. 

So I've got a linegraph showing a typcial early and late curve. 

The "Actual line" is shown as a third line, shown in green.

Now I would realy like the green line to stop repeating the highest value, however I havent found a working solution yet.

the Code behind the green line is as followes:

S-curve_Actual_IFR =

    CALCULATE(

        COUNTA('IFR_P6'[Actual Finish]),

        USERELATIONSHIP(IFR_P6[Actual Finish], Datelist[Date]),

        FILTER(

            ALLSELECTED('Datelist'[Date]),

            ISONORAFTER('Datelist'[Date], MAX('Datelist'[Date]), DESC)

        )

    )

Can anybody help me in the right direction? 

Thanks!

[OwenAuger · Accepted solution]

Hello @JohannesE 

I would suggest following the approach from DAX Patterns:

https://www.daxpatterns.com/cumulative-total/#highlighter_923147

I'm assuming Datelist is market as a date table with Datelist[Date] being the date column.

Here is how I would rewrite your measure, adjusting slightly to use the DAX Patterns pattern, using <= rather than avoiding ISONORAFTER:

S-curve_Actual_IFR =
VAR LastVisibleDate =
    MAX ( Datelist[Date] )
VAR FirstVisibleDate =
    MIN ( Datelist[Date] )
VAR LastDateWithData =
    CALCULATE ( MAX ( IFR_P6[Actual Finish] ), REMOVEFILTERS () )
RETURN
    IF (
        FirstVisibleDate <= LastDateWithData,
        CALCULATE (
            COUNTA ( 'IFR_P6'[Actual Finish] ),
            USERELATIONSHIP ( IFR_P6[Actual Finish], Datelist[Date] ),
            ALLSELECTED ( 'Datelist'[Date] ),
            Datelist[Date] <= LastVisibleDate
        )
    )

This should return this same results as before but with the line ending at the last date with data.

It's possible you might need to tweak LastDateWithData.

Does this work for you?

Regards,

[JohannesE]

Hey owen, 

That works like a charm!

Thanks for your help, now I need to slowly comprihand the applied logic 🙂

Kind Regards

Hans