Category: PowerBI

We are going to address the following limitations:

1. Sometimes you may want to highlight the last two weeks of the month to compare it with the first two weeks of the month, or maybe compare the performance of main KPI’s of the chosen last period.
2. You can observe that the relative date filter of Power BI uses today’s date (17-Jan-2022) as the last date, and sometimes you need that the last week to start on a previous day, not today. For example, if you only have data until 15-Jan-2022 and today it is 19-Jan-2022, when you want to show Last week, you want it to refer to the days between 10-Jan-2022 and 16-Jan-2022…

3. Sometimes you may want to customize the type of periods you see in the slicer. By default, it shows Days, Weeks, Week Calendars, … You may want to reduce that list and show less options.

4. Weeks start on Sunday or on Monday, depending on your regional settings. You may want to force your dashboard to have weeks always start on a Monday, independently of the regional settings of the viewer.

To resolve these problems, we follow the idea of Josh Trewin, a Sport and Data Scientist in his video and went a little bit further.

As the tutorial says, we need to:

We assume that you have a Dates table in your data model, as can be seen below.

Well, you then need to duplicate your Dates table. In the example below, we created ¨Dates_Disconnected¨ table.

Change the Date slicer to dropdown format.

Change the column of ‘Dates’[Date] in the Date slicer for the column ‘Dates_Disconnected’[Date].

Create values for slicer of periods (with the options that suits you), and measure to get the selected value.

PeriodSelected = ALLSELECTED(Periods[Period])

Create measure that returns a number of days depending on the selected period.

PeriodDays = 
SWITCH([PeriodSelected],
    "Week", 6,
    "Month", 30
)

Create metrics that get the max and min of the period we are going to filter:

PeriodEndLast = LASTDATE(Dates_Disconnected[Date])
PeriodStartLast = [PeriodEndLast] - [PeriodDays]
PeriodEndPrev = [PeriodStartLast] - 1
PeriodStartPrev = [PeriodEndPrev] - [PeriodDays]

Add the period slicer to your canvas and set the selection to single select.

Create the magic filter measure. We are filtering the dates in our related table by returning a 1, without losing the dates and also rows in the fact table, before the selected period, and this is why I love this idea, because it allows you to make a comparison with other periods on the same page of your report. This measure works as filter on a visual level, so actually, it doesn’t filter your date table.

DateFilter = 
VAR _Date = MAX('Dates'[Date])
VAR _Filter = 
   IF( _Date <= [PeriodEndLast], 
      IF(_Date >= [PeriodStartLast], 
        1,  
        0 
      ) 
      ,0
    )
return 

_Filter

Now Add this measure to the charts you want to show the period, and this would be the result so far:

What we have left is to make the measure that will show the amount of spending in the last period selected and the amount of spending in the period before that.

SpendPeriod =
VAR _Value = 
    CALCULATE(
        SUM('Internet Sales Fact'[spend]),
        DATESBETWEEN(
            'Dates'[Date],
            [PeriodStartLast],[PeriodEndLast]
        )
    )
RETURN

    _Value

SpendPeriodPrev = 
VAR _Value = 
    CALCULATE(
        SUM('Internet Sales Fact'[spend]),
        DATESBETWEEN(  
            'Dates'[Date],
            [PeriodStartPrev],[PeriodEndPrev]
        )
    )
RETURN 
    _Value

Now just add cards to see the values, periods and voilà, you have a comparison view: