Forum Discussion

rikpatel1998's avatar
rikpatel1998
Frequent Visitor
3 years ago
Solved

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

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

Need Help
  • rikpatel1998's avatar
    rikpatel1998
    3 years ago
    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

     

3 Replies

  • rikpatel1998's avatar
    rikpatel1998
    Frequent Visitor
    3 years ago
    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's avatar
      v-easonf-msft
      Community Support
      3 years ago

      Hi, rikpatel1998 

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

      Best Regards,
      Community Support Team _ Eason

       

      • rikpatel1998's avatar
        rikpatel1998
        Frequent Visitor
        3 years ago

        Hey,

         

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