Custom Predefined Date ranges

I'm trying to create 4 custom date ranges that I can then use as a slicer in my reports. The slicer should have the following options:

Year to Date
Last Month
This Month
This Week

Depending on which option the user clicks on in the report, it should filter the page to that date range. I have a date table already but I can't wrap my head around how to either add columns or another table that sits off of the date table to get the custom ranges to work properly.

Can someone help me out?

Thanks,

Need Help

Anonymous
9 years ago
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:

11 Replies

jwademcg

Advocate I

8 years ago

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

Anonymous

Not applicable

7 years ago

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)

c_murray
Frequent Visitor
6 years ago
Would you have a file you could share?

bmesquita
Frequent Visitor
6 years ago
This is very useful, thanks for your DAX jwademcg.

Now, if I want to have the week starting on a Sunday, what should be the code?

Chihiro
Solution Sage
9 years ago
Can't think of way to do this using single slicer since dates will overlap between each range. Basically you'll need column for each range and toggle "Yes"/"No". Each column needing it's own slicer. But this isn't clean way of doing things.

Instead, why not use Timeline slicer. It can easily toggle between Week, Month, Year.

If your date dimension table contains future dates (ex. dates to end of year), you may need to create another table that shrinks down date range from start of year to current date.
- Anonymous
  Not applicable
  9 years ago
  Thanks for the reply. The reason we want to use this method is that it gives the users a pre-defined set of date filters that they can use with very little effort or thought. We have been using SSRS mobile reports which has a date slicer with this capability and we want to include this capability in our Power BI reports so that both SSRS Mobile and Power BI reports feel similar and have feature parity.
  
  While I don't think I will be able to fully replicate the slicer I would think that giving the user these predefined dates is possilbe. The slicer in mobile reports looks like this.
  - v-huizhn-msft
    Microsoft Employee
    9 years ago
    Hi Anonymous,
    
    I am still confusing about your requirement, and I am nou specific SSRS, several problems need to be confirmed.
    
    1. You want to create a slicer including Year to Date,Last Month,This Month and This Week value?
    
    2. When I select Last Month, it display the last month's value. If current day is 2017/6/21, the last month means 2017/5/1-2017/5/31?
    
    Best Regards,
    Angelia

Forum Discussion

Custom Predefined Date ranges

11 Replies

Related content

How to auto-reset “Custom” date range when switching back to predefined ranges

Custom Date Range Slicer

Create custom predefined date range slicer

Quarter based on predefined date Range

Predefined Spark resource profiles