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