Reusing Master measures in another dimension in a table.

[Lokesh_5045]

Hi Experts, I have two master measures like below. I need to compare both of them and flag them as 'Increase', 'Decrease' and 'Same'.

Master Measure 1: sum({< [Fiscal year/period TEXT] = {"$(vSelected_Month)"} >}  Quantity)
Master Measure 2: sum({< [Fiscal year/period TEXT] = {"$(=Date(Addmonths(Date#('$(vSelected_Month)','MMM YYYY'),-12),'MMM YYYY'))"}  Quantity)

The above 2 measures are sum of quantity of a selected month of current year and same month of last year. Both are working fine.

Now I need to create a dimension to flag based on difference between them.

=if([Master Measure 1] > [Master Measure 2], 'Increase',
If([Master Measure 1] < [Master Measure 2], 'Decrease',
'Same'))

Its not working. But when I create another measure it is working. But its not possible to select the values in a measure. Showing the screenshot below.

In Measure field above, I can't make selection. So without using measures, how can I write the expression for dimension?

[Or]

You would need to write this out using aggr() to assign the correct dimensions for the calculation - your screen capture isn't showing what they are - and you will need to spell out the formulas, as you won't be able to use the master measures, I believe.

[Lokesh_5045]

Hi @Or , I was busy in this week. As you suggested, I tried using Aggr() to write the convert it into dimension. But I couldn't solve it. Please find the exact formulae that I have used for above columns in the screenshot.

1st column formula:
sum({<Region = {'*******'},
[Month] = {"$(=Date(Addmonths(Date#('$(vCurrent_Month)','MMM YYYY'),-12),'MMM YYYY'))"},
[Year] =
>}Tech_Qty)

2nd column formula:
sum({<Region = {'*******'},
[Month] = {"$(vCurrent_Month)"},
[Year]=
>}Tech_Qty)

I have used Region, Month & Year in my set analysis. Can you help me in writing aggr() formula to get required dimension as mentioned in the explanation?