topic Re: Dynamic Year End Calculation in DAX Commands and Tips

Dynamic Year End Calculation

homia1kr — Thu, 30 Jan 2025 16:30:06 GMT

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)
))

Re: Dynamic Year End Calculation

DataInsights — Thu, 30 Jan 2025 17:02:18 GMT

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

Re: Dynamic Year End Calculation

homia1kr — Thu, 30 Jan 2025 17:42:09 GMT

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