Dynamic Year End Calculation

[homia1kr]

Hello!

I have a date table and a table that has a breakdown of # of clients by month end.  I want to be able to pull the sum of the previous year-end number of clients based on the date selected in the slicer.  

I have this formula that works currently, but I'd like to make it dynamic so that I can look at this coming year and last year, rather than having the date hard-coded which means that I can only look at one year in my slicer.

Clients YE 23 = CALCULATE(SUM(Client_Table [SUM (Clients)]),

FILTER(
ALL('Date'[Month end]),
'Date'[Month end] = DATE(2023,12,31)
))

[DataInsights · Accepted solution]

@homia1kr,

Try this measure:

Clients Previous YE =
VAR vSelectedYear =
    YEAR ( MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date] ) )
VAR vResult =
    CALCULATE (
        SUM ( Client_Table[SUM (Clients)] ),
        'Date'[Month End]
            = DATE ( vSelectedYear - 1, 12, 31 )
    )
RETURN
    vResult

[homia1kr]

Thank you SO much! I've been trying everything for the last 2 days. Works perfectly.