June 15, 2020
Monthly and YTD Slicer in Power BI
For Power BI Desktop version 2.76.5678.782 (January 2020).
We usually want to track business data like revenue, plan and forecast on a monthly or YTD basis. YTD or Year to Date is a function, that aggregates data from the beginning of the year up to the current month. We’re usually interested in both views. We can create a slicer, that enables us to simply switch between both views.
Entering data with Enter Data
We start by creating a table in Data view of Power BI. We select Table Tools > New Table. We enter a table as shown on the image below and name it Calculation.
We don’t connect the table to any of the other tables, we need it solely for the purpose of the slicer.
Inserting the slicer
We switch the view to Report and insert the slicer. We check the Calculation field on the field list and select the List view on the slicer. We change the shape of the slicer. We set the orientation to Horizontal and manually change the shape, until we get two buttons like on the image bellow.
We insert the SWITCH() function into a measure we want to observe. In our example we’ll use the measure Actual. We change the formula of measure Actual by inserting the following scenarios:
- In case of selecting Monthly in the slicer, the table Calculation will filter and the only value left in the Calculation[ID] will be 1. We’ll use 1 instruct the SWITCH() function to display the sum of current month.
- In case of selecting YTD in the slicer, the table Calculation will filter and the only value left in the Calculation[ID] will be 2. We’ll use 2 instruct the SWITCH() function to display the sum of values from the beginning of the year up to current month.
The whole formula for the Actual or AC measure is:
AC = CALCULATE(
We can now a plot for the AC measure. Using the Monthly/YTD slicer we can quickly observe the data in both views.