Dynamic measure based on user input

[elliee]

i'd like to create a table in Power BI using two measures that are based on different time period. the report is generated in Tableau today through the combined use of parameters and calculated fields. I'd like to find out if there is a way to acheive this in Power BI.

For example, i'd like to create a table that calculates product margin based on:

1. Revenue from FY2022 January, and

2. Cost from FY2021 November.

The paramater input should be dynami and visible to end users for them to select the time period that they are interested in.

[v-yangliu-msft]

Hi  @elliee ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Measure_Slicer1 =
SUMMARIZE('Table','Table'[Year],'Table'[Month])

Measure_Slicer2 =
SUMMARIZE('Table','Table'[Year],'Table'[Month])

2. Create measure.

Flag =
var _select1year=SELECTCOLUMNS('Measure_Slicer1',"year",[Year])
var _select1month=SELECTCOLUMNS('Measure_Slicer1',"month",[Month])
var _select2year=SELECTCOLUMNS('Measure_Slicer2',"year",[Year])
var _select2month=SELECTCOLUMNS('Measure_Slicer2',"month",[Month])
return
IF(
    AND(
        MAX('Table'[Year]) in _select1year , MAX('Table'[Month]) in _select1month)
    ||
    AND(
        MAX('Table'[Year]) in _select2year , MAX('Table'[Month]) in _select2month),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

[Revenue] and [Cost] set to Sum.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[elliee]

Hi Yang, instead of returning both the cost and revenue for the two selected periods, can we only return the cost for the selected cost period and the revenue for the selected revenue period?

THe end goal is to use these two values to calculate the margin.

e.g. Revenue from FY2022 January / Cost from FY2021 November = Margin

[amitchandak]

@elliee , You can use time intelligence and date table

example measure assume you have measure for revenue and cost

MTD revnue= CALCULATE([Revenue],DATESMTD('Date'[Date]))

2nd  last MTD Cost= CALCULATE([Cost],DATESMTD(dateadd('Date'[Date],-1,MONTH)))

You can use offset also

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[elliee]

Hi Amit, are users required to create a new date field to control the period that they would like to review? are we able to have sth at the visualisation level instead?