Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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