I want to add a slicer for date Hierachy which enables users to choose hierachy e.g. Years or months

[MJ77]

In power BI I have a universal date table i am using for all queries. Its in DateTable[Date]. The date is a daily date but spans many years so if its kept at Day, it can be overly detailed. Therefore, For my line chart, i want to add a slicer where users can choose at what hierachy level they view the chart, i.e. if they want to view it in months, or in years or months, or days etc.

[bhanu_gautam]

@MJ77 , You need a measure that dynamically adjusts based on the selected hierarchy level. 

SelectedDateLevel =
SWITCH(
TRUE(),
ISFILTERED(DateTable[Year]) && NOT ISFILTERED(DateTable[Month]), "Year",
ISFILTERED(DateTable[Month]) && NOT ISFILTERED(DateTable[Day]), "Month",
ISFILTERED(DateTable[Day]), "Day",
"None"
)

Then create a measure  that adjusts the aggregation based on the selected date level

DynamicMeasure =
SWITCH(
[SelectedDateLevel],
"Year", CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(DateTable, DateTable[Year])),
"Month", CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(DateTable, DateTable[Year], DateTable[Month])),
"Day", CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(DateTable, DateTable[Year], DateTable[Month], DateTable[Day])),
SUM(Sales[Amount])
)

Use this dynamic measure in Y-axis

[MJ77]

Thank you however. i want a slicer to be available to users of the report to also select the hierachy level a which they want to view th report