topic Re: Dynamic Month for Year dimension in Bar Graph in App Development

Dynamic Month for Year dimension in Bar Graph

ckyrosis — Thu, 04 Sep 2025 20:12:59 GMT

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

Re: Dynamic Month for Year dimension in Bar Graph

hugo_andrade — Thu, 04 Sep 2025 23:02:09 GMT

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.

Re: Dynamic Month for Year dimension in Bar Graph

ckyrosis — Mon, 15 Sep 2025 18:46:55 GMT

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