Dynamic Month for Year dimension in Bar Graph

[ckyrosis]

As a follow up to my previous post, 

My dimension is 

=If(Match(Year, '2025', '2024', '2023'), Year)

And my expression is 

SUM({<
Month.autoCalendar.Month={"$(=Month(Max(Month)))"},
[Flag] = {'ME'},
[Gender]={'Male'},
[Worker Type]={'Employee'}
>} [eHeadcount]),

This gives me the Male month end headcount for the current Max month across the years in my dimension
-Aug 2025
-Aug 2024
-Aug 2023

I need it to always show month end December for the previous years and max month for current year 
-Aug 2025
-Dec 2024
-Dec 2023

[hugo_andrade · Accepted solution]

Hi @ckyrosis ,

One simple approach is to handle this in the script by creating a couple of helper flags. For example:

• One flag that marks December for all previous years

• Another flag that marks the current month for the current year

Then in your chart you don’t need to wrestle with conditional set analysis. You just use that flag in the set, and it automatically picks the right month for each year.

[ckyrosis · Accepted solution]

I figured it out eventually. Thanks for your answer @hugo_andrade 

Sum(
If(
Year([Month]) = Year(Max(TOTAL [Month]))
and Month([Month]) = Month(Max(TOTAL [Month]))
and [Gender] = 'Male'
and [Worker Type] = 'Employee'
and [Flag] = 'ME',
[eHeadcount],

If(
Year([Month]) < Year(Max(TOTAL [Month]))
and Month([Month]) = 12
and [Gender] = 'Male'
and [Worker Type] = 'Employee'
and [Flag] = 'ME',
[eHeadcount]
)
)
)