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 wo...
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

     
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