Dynamic Measure calculation using other Dynamic Measures and Filters

[toulou]

I have a created a measure table to to enable metric selection in visuals. 

The table Measure_Select looks like this

MEASURE

Measure 1

Measure 2

I am using this table to create a Measure with a Switch function.  

Select a Measure = SWITCH(FIRSTNONBLANK(Measure_Select[Select Measure], Measure_Select[Select Measure]), "Measure 1", SUM(Table[MetricName_Measure_1]),"Measure 2", SUM(Table[MetricName_Measure_2]))

The table and the measurement allow me now to create a slicer where a user can switch between these 2 metrics in a visual. 

So far, so good. 

In addition to those I have create a table where I define Time Periods for dates (e.g. "ThisMonth", "LastMonthToDate", etc.).

This is what I want to solve now. How can I create a Measure now that would bascially do the following:

Divide "Selected Measure for visual (e.g. Measure 1)" with Filter Value "ThisMonth" of Column "Period" by "Selected Measure for visual (e.g. Measure 1)" with Filter Value "LastMonthToDate" of Column "Period". 

As far as I know, I cannot use CALCULATE or SUM because those don't support Created Measures. 

Obviously, I want to use this formular to calculate the change in metric between two periods. 

The challenge is that the measure changes based on the user input and there needs to be a variable. The measure table has 20 different measure names. 

[Anonymous]

Hi @toulou,

>>Obviously, I want to use this formular to calculate the change in metric between two periods. 

The challenge is that the measure changes based on the user input and there needs to be a variable. The measure table has 20 different measure names. 

You can try to add a measure to merge these measures, then use switch or if function to result the specify value.

Result=
var conditionResult=//check the condition to choose a result measure.
return
SWITCH(conditionResult,1,[Measure1],2,"[Measure2]",3,[Measure3],...)

>>As far as I know, I cannot use CALCULATE or SUM because those don't support Created Measures.

No, you can use functions on measure, for example:

SUMX(Table,[Measure])

Calculate(MAXX(Filter(Table,condition),[Measure]),Filter(table,condition))

Regards,

Xiaoxin Sheng

[toulou · Accepted solution]

I am not quite sure why it is working now but somehow it does:

Calc MoM YoY = Calculate(Measure_Select[Select A Measure],Period[Period]="ThisMonth")/Calculate(Measure_Select[Select A Measure],Period[Period]="MoM LastYearToDate")-1

For some reason Calculate accepts the calculated measure now... 

[GilbertQ]

Hi @toulou what about using a Period Table, which will make it easier to create the custom periods that you are after?

And then you can use your periods to easily create the calculations you require also?

http://community.powerbi.com/t5/Data-Stories-Gallery/Create-Dynamic-Periods-for-Fiscal-or-Calendar-D...

I am certain that you can the simply use the Period Names to Dynamically Filter?