Forum Discussion
Percentage Difference - Dynamic Date Axis and Measures
Hi,
I've used the New Parameter Fields option under the Modeling tab on Power BI Desktop to create 2 calculated tables - One for all my measures, the other for date ranges.
I've added them to a line chart visual with 2 filters allowing users to choose the fields they want to add to the visual and how they want the date axis to appear, as below:
Here's an example of how the Date Axis table is set up:
02c: Switch - Date Axis = {
("Date", NAMEOF('02a: Calendar'[Dates]), 0),
("Start of Week DD/MM", NAMEOF('02a: Calendar'[Start of Week DD/MM]), 1),
("End of Week DD/MM", NAMEOF('02a: Calendar'[End of Week DD/MM]), 2),
("Month/Year", NAMEOF('02a: Calendar'[MM/YY]), 3),
("Quarter/Year", NAMEOF('02a: Calendar'[QoY/YY]), 4),
("Year", NAMEOF('02a: Calendar'[Year]), 5)
}
When the users switches the bottom filter it will change the date range from Dates, to Months, Years, etc. Similarly, if the user choose a measure from the measure filter, the visual will show those values.
This is all working as I would like.
The issue I'm having is creating a tooltip that shows the percentage difference by dynamic measure across the dynamic date ranges that can be chosen. When I try setting up a Quick Measure, it doesn't quite let me choose the set up I want:
VAR __BASELINE_VALUE = CALCULATE(VALUES('01d: ALL METRICS'[Fields - All Metrics]), '02c: Switch - Date Axis'[Fields - Date Axis] IN { DATE(2023, 3, 24) })
VAR __MEASURE_VALUE = VALUES('01d: ALL METRICS'[Fields - All Metrics])
RETURN
IF( NOT ISBLANK(__MEASURE_VALUE),
DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE))
From the above you'll see the All Metrics and Date Axis dynamic values. But I need it to reference the previous period rather than a specific date.
Does anyone know how I can achieve this?
Thanks,
Mark
1 Reply
- amitchandak
Super User
Anonymous , In these two blogs, have given examples how to get selected value of axis/field parameter.
Using that you can get axis and create DOD,WOW, MOM etc
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9