Forum Discussion
stalerik
Helper II
6 years agoDate-Range Slicer: Dynamically Change Data Aggregation on Chart based on selected date range
I have added a slicer with dates as a filter to my report. I have a table of one-minute data and created a chart based on it. I would like to change how the data is aggregated on the chart dependin...
edhans
Community Champion
6 years agoSee if this measure helps. You need a data table for this to work.
This first measure just calculates the date range. You could incorporate that into Time Shown measure, but I kept it out for simplicity's sake.
Slicer Days =
DATEDIFF(
FIRSTDATE(ALLSELECTED('Date'[Date])),
LASTDATE(ALLSELECTED('Date'[Date])),
DAY
)
Then this does your actual calculation. I used "General Format" and put the measure in a card. You can get rid of that but you'll need some mechanism so your audience knows you switch from minutes to hours to days. I let anything above 90 also calculate to days to prevent a BLANK error showing up. You can change the math to show weeks or whatever you want.
Time Shown =
SWITCH(
TRUE(),
[Slicer Days] <=3,
FORMAT(SUM('Sample Data'[Minute Data]),"General Number") & " Minutes",
[Slicer Days] > 3 && [Slicer Days] <=30,
FORMAT(SUM('Sample Data'[Minute Data])/60,"General Number") & "Hours",
[Slicer Days] > 30 && [Slicer Days] <= 90,
FORMAT(SUM('Sample Data'[Minute Data])/(60*24), "General Number") & "Days",
[Slicer Days] > 90,
FORMAT(SUM('Sample Data'[Minute Data])/(60*24), "General Number") & "Days"
)