Forum Discussion
Custom Predefined Date ranges
Hi v-huizhn-msft,
Thank you for the suggestion. Unfortunately it didn't work for me in my scenario as it only seemed to work with a single measure and in a model that only has data for the current year. I needed it to work across all measures with data more than 1 year. Although your solution didn't work, I was able to find one that worked with minimal modification to the model and zero dax code on my end. I have to give credit to Chris Webb as I stumbled across his blog post on the topic HERE.
Given that I had a fact table with measures and a date table with dates, I only had to add one more table that I called Date Range that would handle all of the magic.
Here is a look at my model (Only the Date Range table was added):
The Date range table has 3 columns:
- Period - A name column such as (Year to Date, Last Month, etc) that is used as the field on the slicer.
- Date - Contains date values that represent all dates with the specified "Period" column. For example, for the Period Named "Year to Date" currently contains all dates from the 1st of the year to yesterday. Last Month contains all date values for last month. This is important to note as when i drag this field to the Date table the relationship should be Many-to-One (for me it chose one-to-one by default for some reason).
- Sort. This is an arbitray column that allows me to sort the periods in a meaninful way withinin the slicer. (1 for year to date, 2 for last month, etc)
Here is a look at the Date Range table. You can see the duplicate date values for "This Month" and "This Week", hence the many to one relationship with the Date table.
Then, there is just one final piece in order to get this to work. The glorious bi-directional cross filter. In the relationship between date and the Date Range tables, simply change the Cross filter direction from "single" to "Both".
Now, you can enjoy your custom date range slicer inside of your reports:
Abracadabra!
Period =
VAR DateTable = CALENDARAUTO(12)
VAR Today = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],DAY) = 0),"RelativeDate","Today","Sort",1)
VAR Yesterday = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],DAY) = -1),"RelativeDate","Yesterday","Sort",2)
VAR LastWeekday = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1),[Date],DAY) = 0),"RelativeDate","LastWeekday","Sort",3)
VAR ThisWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = 0),"RelativeDate","ThisWeek","Sort",4)
VAR LastWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = -1),"RelativeDate","LastWeek","Sort",5)
VAR ThisMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = 0),"RelativeDate","ThisMonth","Sort",6)
VAR LastMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = -1),"RelativeDate","LastMonth","Sort",7)
VAR ThisYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = 0),"RelativeDate","ThisYear","Sort",8)
VAR LastYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = -1),"RelativeDate","LastYear","Sort",9)
Return
UNION(Today,Yesterday,LastWeekday,ThisWeek,LastWeek,ThisMonth,LastMonth,ThisYear,LastYear)
This should be your period table
and yes this is Chris Webb's method except it is implemented in DAX using the new CalendarAuto function. So you would do the bi-directional between this table and your regular Date Dimension table that joined to your transactional data...again like Chris Webb did. Except he did it in M Language before we had CalendarAuto
Thank you so much for the period table formula! It was super helpful, and I adapted it to include fiscal year categories!
I'd like to pay it forward, so here's my version of the code with Fiscal YTD and Prior Fiscal Year:
Period = VAR DateTable = CALENDARAUTO(12) VAR CurrentWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = 0),"RelativeDate","Current Week","Sort",1 ) VAR PriorWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = -1),"RelativeDate","Prior Week","Sort",2 ) VAR CurrentMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = 0),"RelativeDate","Current Month","Sort",3 ) VAR PriorMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = -1),"RelativeDate","Prior Month","Sort",4 ) VAR YearToDate = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = 0),"RelativeDate","Year To Date","Sort",5 ) VAR PriorYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = -1),"RelativeDate","Prior Year","Sort",6 ) VAR FiscalYTD = ADDCOLUMNS(FILTER(DateTable, IF(MONTH([Date]) <= 6, YEAR([Date]), YEAR([Date])+1) - IF(MONTH(TODAY()) <= 6, YEAR(TODAY()), YEAR(TODAY())+1) = 0), "RelativeDate","Fiscal YTD","Sort",7 ) VAR PriorFiscalYear = ADDCOLUMNS(FILTER(DateTable, IF(MONTH([Date]) <= 6, YEAR([Date]), YEAR([Date])+1) - IF(MONTH(TODAY()) <= 6, YEAR(TODAY()), YEAR(TODAY())+1) = -1), "RelativeDate","Prior Fiscal Year","Sort",8 ) Return UNION(CurrentWeek,PriorWeek,CurrentMonth,PriorMonth,YearToDate,PriorYear, FiscalYTD, PriorFiscalYear)Would you have a file you could share?