How to get max of month based on financial year selection

[abc_18]

Hello everyone,

Need one help related to financial year issue.

I have added fiscal year calendar in the  script itself, now in KPI I have to show max of month of a selected fiscal year data.

Below is the expression I am using, 

=sum({<FMonth={$(vMaxMonth)},fYear={$(vMaxYear)}>}sales)

But here vMaxMonth variable, not giving correct value, it always gives 12.

for fYear (2020),  it should show March month data as max of financial year.

Please suggest, what changes needs to be done for variable.

Regards

[Vegar]

I usually create my fiscal month in this way. (The 4 represents April beeing the first month of the fiscal year)

Dual( Month([%datekey]), Month(Monthname([%datekey],1-4))) AS FMonth,

Using this definition of Fiscal month will order the month correctly and your max will return March month.

I hope it can be of help for you.

[abc_18]

Hi Vegar,

Thanks for the  response.

I have used your expression, it does order the months correctly from Apr to March, but still if I use Max(Fmonth), it's giving value as 12.

Regards

[abc_18]

Just I tried with Maxstring function, like Maxstring(Fmonth), and this one is giving me 'Mar'.

Regards

[Vegar · Accepted solution]

12 is represented by the string 'March' in the FMonth dual