Forum Discussion

elliee's avatar
elliee
Frequent Visitor
3 years ago

Dynamic measure based on user input

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.

 

General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      elliee
      Frequent Visitor
      3 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      elliee
      Frequent Visitor
      3 years ago

      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