Dynamic bar chart measure using field parameter selection, Measure of same week previous year

[rikpatel1998]

Hey all,

I have a bar chart which is using the field parameter feature that allows the end user to change metrics, the chart plots some selected metric (X, Y, Z) against the week number.

I would like to add another dynamic metric which shows the same metric as the one selected in the slicer but for the same weeks last year.

I have figured out how to get the selected value from the field parameter selection via this measure:

Selectedmeasuretest =

 VAR __SelectedValue =

    SELECTCOLUMNS (

        SUMMARIZE ( 'Switch Metric', 'Switch Metric'[Switch Metric], 'Switch Metric'[Switch Metric Fields] ),

        'Switch Metric'[Switch Metric]

    )

RETURN IF ( COUNTROWS ( __SelectedValue ), __SelectedValue )

To dynamically change the metric depending on the switch metric selection, I believe you can use nested if statements, i.e.

IF( selectedmeasuretest = X, X from 2019, IF(selectedmeasuretest  = Y, y from 2019.....) etc.

However I am having trouble to get the same set of weeks for the previous year (in my case, 2022 vs 2019). I have tried using the dateadd function and doing dateadd(calendar[date], -1095, day) however this doesn't work because the week numbers within the years don't seem to line up equally with the date. e.g week 29 2022 = 10 july, but week 29 2019 is 11 july or something like that.

 

The last week of the 2019 measure is missing (the line in the photo). And there is a date slicer that can be used at the top of the page.

My calendar table looks like this: 

Is there a way I can get 2019 data for the same weeks e.g in above 26-29?

Thanks

[rikpatel1998 · Accepted solution]

I may have created the solution, instead of using dateadd, I have got the min and max week numbers from the date slicer and used that in the Calculate function to retrieve the 2019 data.

2019 measure =

var maxweek = MAX('Calendar'[Week Number])

var minweek = MIN('Calendar'[Week Number])

var X = CALCULATE([metricX], all('Calendar'[Date]), 'Calendar'[Year] = 2019, 'Calendar'[Week Number] >= minweek && 'Calendar'[Week Number] <= maxweek)

var Y = CALCULATE([metricY], all('Calendar'[Date]), 'Calendar'[Year] = 2019, 'Calendar'[Week Number] >= minweek && 'Calendar'[Week Number] <= maxweek)

var Z = CALCULATE([metricZ], all('Calendar'[Date]), 'Calendar'[Year] = 2019, 'Calendar'[Week Number] >= minweek && 'Calendar'[Week Number] <= maxweek)

var test = IF([Selectedmeasuretest] = "metricX", X, IF([Selectedmeasuretest] = "metricY", Y, IF( [Selectedmeasuretest] = "metricZ", z)))

return test

Note: the X Y and Z in selectedmeasuretest refers to the name of the metric in the field parameters slicer.

I will double check and see if this works and mark solution as solved if so.

Thanks

[v-easonf-msft]

Hi, @rikpatel1998 

Could you please tell me whether your problem has been solved?

Best Regards,
Community Support Team _ Eason

[rikpatel1998]

Hey,

Sorry, forgot to mark the comment as the solution  - this problem has been resolved