Help needed on Max(Month)

[#1]

Hi All,

Hope all are doing good...

I need your help to solve one of the issue that i am facing.

I have a date field in DD/MM/YYYY and i have retrieved Month and Year from that field.

Now the issue is that when i am writing the Max(Month) expression to select Maximum month it is returning Month number instead of Month Name.As a result the value is not getting selected in the month field.

However I have used Month(Max(Date)) to get maximum month and i am able to select maximum month with that expression.

But at the same time for YTD calculation i have used Sum({<Year={"$(=Max(Year))"},Month={"$(=Month(Max(Date)))"}>}Sales) which returning '0'.

Please help me with the correct approach to resolve the issue.

Thanks in advance.

Best Regards,

Nani

[Anonymous]

Yep, it will work as you have ignored Year & Month not Date

[sunny_talwar]

Yes, I am certain it will and reason is by making selection in year and month, your max date will also change which will drive this expression

[sunny_talwar · Accepted solution]

It seems you are looking for a YTD expression and not MTD. For YTD, try this:

Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date)), 'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'DD/MM/YYYY'))"}Year = ,Month = >}Sales)

[#5]

Thanks Balraj and Sunny.

I will try these options.

[Anonymous]

It seems either we did not get you or you are not able to explain.

Check these threads, will help you a lot..

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

[Colin-Albert]

How has your Month data been created? It should be a dual field with both text and number parts.

Can you create a chart with Month as a Dimension and Num(Month) and text(Month) as Expressions.

If you dates are dual you will have both numeric and text values.

[#8]

Thanks Sunny. It worked for me.

Thanks Balraj and all for your valuable inputs.