cumulative measure in line chart

[WM117]

Hi everybody,

I'm stuck with an issue that is bothering me for quite some time now. The orange line in the line chart below represents the cumulative written hours n a project. The hours are registered untill july, after that there is no data. As you see, the line continous untill the end of the year. I want to create a dax measure (or another solution if that's easier), that cuts off the orange line after the the point to which there is no more data available. 

Currently I use this measure: 

Cumulative_Geschreven Uren = 
            CALCULATE(SUM('Urenregistratie'[Geschreven uren]),
            FILTER(ALLSELECTED( DateTable),
            DateTable[Date] <= MAX( DateTable[Date])))

ChatGPT recommened me this measure, but in this case the line cuts off in June, while there is data from the month July (see picture below). In this case i used the following DAX.

Cumulative_Geschreven Uren (Cutoff) = 
VAR CurrentDate =
    MAX(DateTable[Date])
VAR LastDateWithData =
    CALCULATE(
        MAX(DateTable[Date]),
        FILTER(
            ALLSELECTED(DateTable),
            CALCULATE(SUM('Urenregistratie'[Geschreven uren])) <> 0
        )
    )
RETURN
IF(
    CurrentDate <= LastDateWithData,
    CALCULATE(
        SUM('Urenregistratie'[Geschreven uren]),
        FILTER(
            ALLSELECTED(DateTable),
            DateTable[Date] <= CurrentDate
        )
    )
)

Any solution would be greatly appreciated! 

Willem

[WM117]

Hi Johnt75,

Your edit worked! Thank you so much! I've accepted it as a solution. Good weekend to you!

[johnt75 · Accepted solution]

The code is nearly correct, I think that the problem is because you are showing the data at the month granularity. The value returned by MAX( DateTable[Date] ) will return the 31st July, whereas the LastDateWithData could be e.g. 15th July.

You can fix it by checking the current date against the end of the last month with data in it.

Cumulative_Geschreven Uren (Cutoff) =
VAR CurrentDate =
    MAX ( DateTable[Date] )
VAR LastDateWithData =
    EOMONTH (
        CALCULATE (
            MAX ( DateTable[Date] ),
            FILTER (
                ALLSELECTED ( DateTable ),
                CALCULATE ( SUM ( 'Urenregistratie'[Geschreven uren] ) ) <> 0
            )
        ),
        0
    )
RETURN
    IF (
        CurrentDate <= LastDateWithData,
        CALCULATE (
            SUM ( 'Urenregistratie'[Geschreven uren] ),
            FILTER ( ALLSELECTED ( DateTable ), DateTable[Date] <= CurrentDate )
        )
    )