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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JohannesE
Regular Visitor

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

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.

JohannesE_0-1687360358520.png

 

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!

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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,


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

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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,


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

Hey owen, 

That works like a charm!

 

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

 

Kind Regards

Hans

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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