TotalYTD until last existing Date

[Rygar]

Dear Community,

I would like to calculate the sum of spendigs for every year until the last existing month in my datetable to compare the spendings.

Example:
Last date in my Datetable is the 5th of Feb, so I need the Spendings until Feb for every Year existing in my Datetable.

This works fine:

Spend latest Month = 
VAR LM=TOTALYTD(
    SUM(Spend[GrossSpend]),
    dDate[Date],
    MONTH(dDate[Date])<=2
)
RETURN LM

PROBLEM: I have to manually enter the <=2 (Feb) in this Expression.

Something like this does not bring the correct results:

MONTH(dDate[Date])<=MONTH(MAX(dDate[Date]))

Any Idea?

Thanks and regards,
Michael

[AlexisOlson]

Since you aren't using TOTALYTD quite how it's designed, you probably don't want that exact function.

See if something like this works for you:

Spend latest Month =
VAR _LastMonth = MONTH ( MAX ( dDate[Date] ) )
VAR LM =
    CALCULATE (
        SUM ( Spend[GrossSpend] ),
        KEEPFILTERS ( MONTH ( dDate[Date] ) <= _LastMonth )
    )
RETURN
    LM

Note: I'm assuming you already have the year in your filter context. 

[Rygar]

Thank you very much! The Expression works fine, when I set _LastMont to a fixed value (March):

KEEPFILTERS ( MONTH ( dDate[Date] ) <= 3 )

But when I use a dynamic Value _LastMonth:

KEEPFILTERS ( MONTH ( dDate[Date] ) <= _LastMonth )

the Result is completely different and for me wrong. I don't understand this.

Example File here.

[AlexisOlson · Accepted solution]

Ah, you need to remove the local filter context when computing the last month.

Spend latest Month =
VAR _LastMonth = CALCULATE ( MONTH ( MAX ( dDate[Date] ) ), ALL ( dDate ) )
VAR LM =
    CALCULATE (
        SUM ( Spend[GrossSpend] ),
        KEEPFILTERS ( MONTH ( dDate[Date] ) <= _LastMonth )
    )
RETURN
    LM

[Rygar]

Now I learned again! Thanks a lot for this solution!