Different date periods - YTD,current month, previous month in a filter

[Dz6kb4]

Hi all.

I'm looking to be able to filter my data based on different date periods ie. YTD, current month, previous month.  However, I would like to be able to select one of these from a drop down filter.  is this possible?

Many thanks.

[Phil_Seamark · Accepted solution]

Hi @Dz6kb4

Sure.  Just create a measure table and don't connect it to anything

The table may contain data like :

I called my table Date Range

ID    Value
--     --------
1       This Month
2       Current Month
3       Last Month

Then add that table to a slicer but don't create any relationships between this table and your date table.

Then create a measure on your Date table that uses DAX to determine if your date is part of the grouping. 

Date Range Filter = 
VAR ThisMonth = TODAY()
VAR LastMonth = EOMONTH(TODAY(),-1)
VAR PrevMonth = EOMONTH(TODAY(),-2)
RETURN
SWITCH( 
			TRUE() , 
			-- Current Month
			MAX('Date Range'[ID])=1 
			&& MONTH(MAX('Dates'[Date])) = MONTH(ThisMonth) 
			&& YEAR (MAX('Dates'[Date])) = YEAR(ThisMonth) , 1 ,
			-- Last Month
			MAX('Date Range'[ID])=2 
			&& MONTH(MAX('Dates'[Date])) = MONTH(LastMonth) 
			&& YEAR (MAX('Dates'[Date])) = YEAR(LastMonth) , 1 ,
			-- Last Month
			MAX('Date Range'[ID])=3
			&& MONTH(MAX('Dates'[Date])) = MONTH(PrevMonth) 
			&& YEAR (MAX('Dates'[Date])) = YEAR(PrevMonth) , 1 ,			
			
			-- ELSE
			0)

Then just drag this measure in as a filter on your visual and set that it must Equal = 1

Grab this PBIX file for a working copy

https://1drv.ms/u/s!AtDlC2rep7a-kHP_xOzy4vBRgbEH

[Dz6kb4]

Wow. This is brilliant thanks alot. Do you know what the dax is for year to date to add also?
Many thanks for your help

[Phil_Seamark]

Hi @Dz6kb4

If you want the current year that's easier.

Date Range Filter = 
VAR ThisMonth = TODAY()
VAR LastMonth = EOMONTH(TODAY(),-1)
VAR PrevMonth = EOMONTH(TODAY(),-2)
RETURN
SWITCH( 
			TRUE() , 
			-- Current Month
			MAX('Date Range'[ID])=1 
			&& MONTH(MAX('Dates'[Date])) = MONTH(ThisMonth) 
			&& YEAR (MAX('Dates'[Date])) = YEAR(ThisMonth) , 1 ,
			-- Last Month
			MAX('Date Range'[ID])=2 
			&& MONTH(MAX('Dates'[Date])) = MONTH(LastMonth) 
			&& YEAR (MAX('Dates'[Date])) = YEAR(LastMonth) , 1 ,
			-- Last Month
			MAX('Date Range'[ID])=3
			&& MONTH(MAX('Dates'[Date])) = MONTH(PrevMonth) 
			&& YEAR (MAX('Dates'[Date])) = YEAR(PrevMonth) , 1 ,
			-- This year
			MAX('Date Range'[ID])=4
			YEAR (MAX('Dates'[Date])) = YEAR(PrevMonth) , 1 ,	
-- ELSE 0)