Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rossnruthie
Resolver I
Resolver I

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,

1 ACCEPTED SOLUTION

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):

Capture.PNG

The Date range table has 3 columns:

  1. Period - A name column such as (Year to Date, Last Month, etc) that is used as the field on the slicer.
  2. 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).
  3. 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.

Capture.PNG

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".

 

 

 

Capture.PNG

Now, you can enjoy your custom date range slicer inside of your reports:

Capture.PNG

 

View solution in original post

11 REPLIES 11
jwademcg
Advocate I
Advocate I

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

 

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?

Anonymous
Not applicable

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?

Chihiro
Solution Sage
Solution Sage

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.

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.

Capture.PNG

Hi @rossnruthie,

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

Hi @v-huizhn-msft

 

 

Yes, I want a slicer that has the 4 options listed above that will filter the data to the specified date ranges.  To help visualize this I have create a calculated table that has the following values:

Capture.PNG

And the slicer could look something like this

Capture.PNG

So when the the user clicks last month on the slicer for example, the data should be filtered to only show values that fall within that date range of 5/1/2017 through 5/31/2017.  If they then click "This Week", it should then filter to data from 6/18/2017 through 6/20/2017.

 

I'm just not sure how to hook this up to my date table in order to get the functionality to work.

Hi @rossnruthie,

I got it now. I create the following sample table. Date is from 2017/1/1 to 20177/6/20. 

1.PNG

First, create calculated column to get Year, Month,Week.

Year = YEAR(Test[Date])
Month = MONTH(Test[Date])
Week = WEEKNUM(Test[Date])


Second, create tree measures useing the formulas.

Year to date = CALCULATE(MAX(Test[value]),FILTER(Test,Test[Year]=YEAR(MAXX(ALL(Test),Test[Date]))))
this month = CALCULATE(MAX(Test[value]),FILTER(Test,Test[Month]=MONTH(MAXX(ALL(Test),Test[Date]))))
last month = CALCULATE(MAX(Test[value]),FILTER(Test,Test[Month]=MONTH(MAXX(ALL(Test),Test[Date]))-1))
this week = CALCULATE(MAX(Test[value]),FILTER(Test,Test[Week]=WEEKNUM(MAXX(ALL(Test),Test[Date]))))


Third, create a new table including the values.

2.PNG

Forth,  create a measure using the formula.

result = SWITCH(FIRSTNONBLANK(Range[Date Range],Range[Date Range]),
	"Year to Date",Test[Year to date],
	"This Month",Test[this month],
	"Last Month",Test[last month],
	"This week",Test[this week])


Finally, create a slicer including Range[Date Range], create a tabke show the table values.

4.PNG5.PNG7.PNG

Thanks,
Angelia

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):

Capture.PNG

The Date range table has 3 columns:

  1. Period - A name column such as (Year to Date, Last Month, etc) that is used as the field on the slicer.
  2. 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).
  3. 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.

Capture.PNG

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".

 

 

 

Capture.PNG

Now, you can enjoy your custom date range slicer inside of your reports:

Capture.PNG

 

 

I have applied this DatePeriod solution, and it works to filter most measures, except those that is looking at prior period outside of the visual context.

Because the new DatePeriod table has already filtered the CreateDateTable, this formula will not go fetch the range of the prior year to give me a Year over Year value.

 

Cumulative YTD YOY Value =
CALCULATE( [Opportunity Actual Value],
FILTER( ALL( CreateDateTable ),CreateDateTable[Year] = VALUES(CreateDateTable[Year]) &&
CreateDateTable[DayOfYear] <= MAX( CreateDateTable[DayOfYear] ) ))

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.