VirajR's Blog

Select the relative dates (weeks, Months, Years) from a slicer.

Select the relative dates (weeks, Months, Years) from a slicer.

Viraj Rathnayaka's photo
Viraj Rathnayaka
·

3 min read

The dates filter is the most commonly used filter in Power BI Reports. We mostly add a simple drop-down or a slicer with dates, months quarters, etc.

Instead of using specific date columns for filtering some users/projects may find it useful to have a limited number of relative date terms.

For example choices like last week, current week, next week, etc.

This post will show you how to filter data by relative periods.

  • Last Week, This Week, Next Week filter

  • Last Month, This Month, Next Month filter

  • Last Year, This Year, Next Year Filter

Data Table

we have a sales orders table with dates and sales amounts.

Last Week, This Week, Next Week filter

We need to assign each sales order one of these 3 terms based on the order date.

Let's add a new column for relative periods in weeks.

Here is the formula.

if  Date.IsInCurrentWeek( [Order Date] ) 
then "Current Week"

else if  Date.IsInNextWeek( [Order Date] )
then "Next Week"

else if  Date.IsInPreviousWeek ( [Order Date] ) 
then "Previous Week"

else "Out of Range"

Function - IsInNextWeek ( )

The function we use here is the IsInNextWeek( ) function M language. It checks the given date or datetime column if it's in the next week or not and returns a True/False value.

Date.IsInNextWeek( [datetime] )

In the formula, we use this as the condition of the if( ) function and it returns the relative date term in text (Next Week).

The other 2 functions ( IsInCurrentWeek, IsInPreviousWeek ) are used similarly for respective terms.

Click Close & Apply.

add a slicer and add a new column to the slicer.

Last Month, This Month, Next Month filter

We need to assign each sales order one of these 3 months' terms based on the order date.

Let's add a new column (Months) for relative periods in months.

Here is the formula.

if  Date.IsInCurrentMonth ((  [Order Date])) 
then "Current Month"

else if  Date.IsInNextMonth  ([Order Date])
then "Next Month"

else if  Date.IsInPreviousMonth (([Order Date])) 
then "Previous Month"

else "Out of Range"

Function - IsInNextMonth ( )

The function we use here is the IsInNextMonth ( ) function M language. It checks the given date or datetime column if it's in the next month or not and returns a True/False value.

Date.IsInNextMonth  ( [datetime] )

In the formula, we use this as the condition of the if( ) function and it returns the relative date term in text (Next Month).

The other 2 functions ( IsInCurrentMonth, IsInPreviousMonth ) are used similarly for respective terms.

Click Close & Apply.

add a slicer and add a new column to the slicer.

Last Year, This Year, Next Year Filter

We need to assign each sales order one of these 3 terms based on the order date.

Let's add a new column for relative periods in weeks.

Here is the formula.

if  Date.IsInCurrentYear ((  [Order Date])) 
then "Current Year"

else if  Date.IsInNextYear  ([Order Date])
then "Next Year"

else if  Date.IsInPreviousYear (([Order Date])) 
then "Previous Year"

else "Out of Range"

Function - IsInNextYear ( )

The function we use here is the IsInNextYear ( ) function M language. It checks the given date or datetime column if it's in the next year or not and returns a True/False value.

Date.IsInNextYear( [datetime] )

In the formula, we use this as the condition of the if ( ) function and it returns the relative date term in text (Next Year).

The other 2 functions ( IsInCurrentYear, IsInPreviousYear ) are used similarly for respective terms.

Click Close & Apply.

add a slicer and add a new column to the slicer.

PowerQueryPowerBI