Forum Discussion
Solved
Dynamic Hierachy depending on date range ?
Hello Everyone, I'm finalizing a report and this what I would like to achieve I have many indicator, and for some of them my timeperiod is pretty big (more thant 20 years) and for some it is pre...
In Power BI, dynamic axes on visuals require a workaround since measures cannot directly be used as axis fields.
Create a Calculated Column for the Axis Values
Dynamic Axis =
VAR MinDate = MIN('Table'[Date])
VAR MaxDate = MAX('Table'[Date])
VAR DateRangeYears = DATEDIFF(MinDate, MaxDate, YEAR)
RETURN
IF(
DateRangeYears < 3,
FORMAT('Table'[Date], "MMM yyyy"),
FORMAT('Table'[Date], "yyyy")
)
To ensure the axis sorts correctly, create an additional column for sorting:
Axis Sort =
IF(
DATEDIFF(MIN('Table'[Date]), MAX('Table'[Date]), YEAR) < 3,
YEAR('Table'[Date]) * 100 + MONTH('Table'[Date]),
YEAR('Table'[Date])
)
Sort the Dynamic Axis column by this column.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn