jeudi 25 novembre 2021

DAX Conditional Calendar Table in Power BI with Switch or if statement

Goal: to build a dynamic calendar table that is conditional upon a slicer selection.

Context: To reduce the size of the dashboard I'm building, data is provided at a daily level for the most recent 90 days and aggregated monthly for the previous 18 months. A date slicer is used to filter data, but I want the range of this slicer to reflect the date range being used.

The goal is for the user to choose the time period from a slicer which then filters the start and end date of the date slicer.

Period Selector and Date Slicer Pane

My impression is that maybe it's not possible for a calendar table to be dynamic like this, but would very much appreciated confirmation of that! Thanks!

See below the calculated table I have been trying.

Period_D_Metric1# = 
VAR _latestmonth = CALENDAR(max(change_mstart[change_mstart]), max(metric_mend[metric_mend]))
VAR _latestquarter = CALENDAR(max(change_qstart[change_qstart]), max(metric_qend[metric_qend]))
VAR _custommonth = CALENDAR(min(AvailabilityD[Date]), max(AvailabilityD[Date]))
VAR _customquarter = CALENDAR(min(AvailabilityM[Month]), max(AvailabilityM[Month]))
RETURN
Switch(
    TRUE(),
    SELECTEDVALUE('Table'[Period]) = "Latest Month",
        _latestmonth,
    SELECTEDVALUE('Table'[Period]) = "Latest Quarter",
        _latestquarter,
    SELECTEDVALUE('Table'[Period]) = "Custom Daily",
        _custommonth,
    SELECTEDVALUE('Table'[Period]) = "Custom Monthly",
        _customquarter
)

Aucun commentaire:

Enregistrer un commentaire