Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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?