Power BI Tips: Dynamic Month Slicer Selection in 4 steps

We used to create a lot of reports based on monthly snapshot data. It requires a mandatory filter based on Year Month and users like to see the latest month data as the default view. We don’t have any straightforward option or feature to create dynamic slicers in Power BI. This blog will give a workaround to achieve such requirements. Follow the below steps,

Step 1: Create a Date Table with the below columns,

• Date
• Month Year (MMM-YYYY)
• YYYYMM (Year month Id)

If you are using DirectQuery mode, you can bring the Date dimension from your database. We need the YYYYMM column to sort the Month Year column since it is a text datatype.

If you are using Import mode, there are multiple methods to create a Date table. In this example, I used CALENDAR and FORMAT DAX functions.

• DimDate = CALENDAR(“2020-01-01”,EOMONTH(TODAY(),-1))
• Month Year = FORMAT(‘DimDate'[Date],”MMM-YYYY”)
• YYYYMM = FORMAT(‘DimDate'[Date],”YYYYMM”)

Step 2: Create a flag column – Latest Month

Latest Month = IF(MONTH(‘DimDate'[Date]) = MONTH(MAX(DimDate[Date])) && YEAR(‘DimDate'[Date]) = YEAR(MAX(DimDate[Date])), “Latest Month”, “Past Months”)

This logic will mark the latest month dates as Latest Month and others as the Past Month.

Step 3: Create a Hierarchy Slicer – Add Latest Month and Month Year Columns to the slicer

Step 4: Select Latest Month in the slicer as the default selection

It will dynamically update when you get next month’s data.

Hope this blog helps to achieve a dynamic month slicer. Please feel free to reach Cittabase for more information. Visit our blogs for more topics on Power BI.