The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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
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
@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
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |