Create Dynamic Date Periods in DAX

Date filters are a very common dimension in dashboards and here is a simple way to create dynamic date filters. This saves times as it prevents you from having to pull dates from other sources.

In other tables, I would add a DateKey as follows...

DateKey = Format('Sales[DATE],"YYYYMMDD")


Then the following...
DATES


DATE = ADDCOLUMNS( CALENDAR(DATE(2017,1,1), DATE(2020,12,31)) ,"DateKey", FORMAT ( [Date], "YYYYMMDD" ), //NumericDate
"Date2", FORMAT ( [Date], "M/d/yyyy" ),

"Year", YEAR([Date]),

"FiscalYear","FY" & IF (MONTH([Date]) <=5 ,Year([Date]),Year([Date]) +1) ,

"Quarter", "Q " & FORMAT( [Date], "Q"),

"MonthNameLong" , FORMAT ( [Date], "mmmm" ) ,

"MonthNameShort" , FORMAT ( [Date], "mmm" ) ,"Month Key", FORMAT ( [Date], "YYYYMM" ),

"MonthNumber", MONTH([Date]),

"MonthYear", FORMAT ( [Date], "mmm " ) & YEAR([Date]), "Month Year 2", FORMAT ( [Date], "mmm " ) & RIGHT(YEAR([Date]),2),

"DayName", FORMAT ( [Date], "dddd" ), //Name for Each day of the week

"DayShortName", FORMAT ( [Date], "ddd" ),

"DayNumber" , WEEKDAY ( [Date] ) )

DATE PERIOD

 DatePeriod = 

UNION (

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-07+1,today()) ), 'Date'[Date]),"Period","Last 07 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-14+1,today()) ), 'Date'[Date]),"Period","Last 14 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-30+1,today()) ), 'Date'[Date]),"Period","Last 30 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-90+1,today()) ), 'Date'[Date]),"Period","Last 90 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall") )




 Once you create both date and date period tables, you can need to join these together using Manage Relationships. Make sure you specific to Both for Cross Filter Directions.