Using set analysis to get Max Value regardless of selection

[#1]

Hello!

I'm trying to compare Prior Year and Current year, so what I'm doing is grabbing the current [Year Month] selection, subtracting it by 100

and using set analysis to force it onto the expression:

=num(sum({<[Year Month] =  {$(=v_MaxYearMonth-100)}, [Type] = {'P'}>}[Count]),'#,##0')

I have set as v_MaxYearMonth =Max({1}[Year Month])

I verified that v_MaxYearMonth works, but the sum expression does not for all [Year Month] selections, it returns the sum only if the selection is 201305 (which incidentally is this 201405 - 100), but returns 0 otherwise

Maybe I'm missing something obvious, but I've been pondering on this for hours, and I got nothing...

Any ideas?!

[SergeyMak]

To get same month of previous year I usually use Month={$(=Month(AddMonths(Max(Calendar.Date), -12)))},

Year={$(=Year(AddMonths(Max(Calendar.Date), -12)))}, Calendar.Date=

If you'd like advice for your approach, please share a sample app.

[jagan]

HI,

Why you are subtracting 100 from v_MaxYearMonth, do you want what is your Year Month format, if you use -100 the month is returned as numeric value, not as month, that is why the expression is not working, can you attach some sample data and expected output.

Try this expression, hope it helps you

=num(sum({<[Year Month] =  {"$(=Date(Date#(v_MaxYearMonth, 'YYYYMM')-100, 'YYYYMM'))"}, [Type] = {'P'}>}[Count]),'#,##0')

Regards,

Jagan.

[#4]

Thank you, actually my year month format is YYYYMM so for example current year month is 201405

[jagan]

Hi,

Is that expression working?

Regards,

Jagan.

[#6]

No, that expression didn't work.

I also have Month and Year as separate values (i get to them by trimming {Year Month]) I got an idea of putting "Year = , Month = " in the set analysis, but that didn't work either.

[#7]

For the record, it works when i DO select 201305 in my selections.. WEIRD..

[jagan]

Hi,

Why you are subtracting -100 in your expression? Can you comeup with sample file and expected output?

Regards,

jagan.

[#9]

subtracting 100 to get to last year. i'm trying to show the results of last year compared to this year.

So,

When a user selects 201405, i have one expression showing a result for that. then i want to use a set expression to show what happened in 201305

[jagan · Accepted solution]

Hi,

Try this

=num(sum({<YearDimensionName={'$(=Max(YearDimensionName) -1)'}, [Type] = {'P'}>}[Count]),'#,##0')

Replace Year with your YearDimensionName.

or

=num(sum({<[Year Month] =  {"$(=Date(MonthStart(Date#(v_MaxYearMonth, 'YYYYMM')-100, -12), 'YYYYMM'))"}, [Type] = {'P'}>}[Count]),'#,##0')

Regards,

Jagan.