Forum Discussion
Creating Measure to switch between Daily, Weekly, Monthly, Yearly
Hello,
I have temperature values coming every 15 minutes (two columns - value and time). Is it possible to create a measure that calculates the average Hourly, Daily, Weekly, Monthly and somehow incorporated into a slicer that would control a line chart?
I created separate columns for date time (Hour, Day, Week and Month), but I have troubles creating a measure that will calculate the average depending on the slider.
Any help/direction would be much appreciated.
Regards,
Laurent
So you would like to have the x-axis to change depending on the value chosen in the slicer? As far as I know a measure cannot be set on the axis.
One solution could be using bookmarks instead. Create the four charts for each time period above eachother and use buttons assigned with bookmarks to toggle between the charts.
7 Replies
lrnt12 Please try using SWITCH and SELECTEDVALUE funtions...
Sample data and expected output will be helpful to provide an accurate solution.
Thanks for the reply.
My data looks like this. Value and Datetime field is what I pull from the source.
The expected output should be a slicer that would control the line chart. When I select Daily, I should see the average value for each day.
Basically, instead of having to drill up and down on the line chart, I'd like to control this with a slicer.
I have tried using SWITCH and SELECTEDVALUE, but it doesn't seem to work. Or perhaps I haven't used it right.
Thanks,Laurent
So you would like to have the x-axis to change depending on the value chosen in the slicer? As far as I know a measure cannot be set on the axis.
One solution could be using bookmarks instead. Create the four charts for each time period above eachother and use buttons assigned with bookmarks to toggle between the charts.
Hi Laurent,
Maybe something like this?
Avg Temp Dynamic =
SWITCH(
TRUE();
SELECTEDVALUE('Calculation'[Calculation]) = "Hourly"; [Avg Temp Hourly];
SELECTEDVALUE('Calculation'[Calculation]) = "Daily"; [Avg Temp Daily];
SELECTEDVALUE('Calculation'[Calculation]) = "Weekly"; [Avg Temp Weekly]
)In this case I created a table with the different alternatives to choose between the calculations. Then I've created the different measures seperately for each calculation and just referenced them in this dynamic measure.
Hope this helps!
