cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MAwbre Frequent Visitor
Frequent Visitor

Relational Date Filter By Other Date (How To)

Abstract:

By leveraging measures, you can achieve relative filtering by any date, not just the current one.  This is required because a great deal of time and date based data has no relation to current time and date, and relative date filtering can only be in relation to the current date.

 

Use Case:

When looking at a report, my clients wanted to be able to see the reports from previous days as well using a simple date slicer at the top.  The report they want included information such as a chart showing the 7 days before and after the selected date.

 

Research and Findings:

When exploring options with Power BI, most people simply state that this is not a possible task, because it would require using a "Relative Date" filter, being filtered by a date from a slicer, esssentially a variable.  I came across the following solution to a quite different problem during my search.

 

Measure = CALCULATE(sum(Transactions[Amount]), FILTER(ALL(Transactions), Transactions[Transaction Date] <MAX(Transactions[Transaction Date])))

I started to break this down and, while learning a whole lot about DAX, realized that this method could be applied to dates alone.  The first step was to make 2 calendar tables from my data.  One calendar was to be used for the slicer (I called it the Frozen Calendar), which also proves very sueful for calculating measures that involve only that single date.  After the user selects a date, I have the following measure which returns that selected date.

 

Selected Date = SELECTEDVALUE('Frozen Calendar'[Date], today())
  • The today() bit is simply the best alternative in my use case when multiple days are selected, please make sure you understand what is best for your case before applying.

Then in my second calendar (I called the Relational Calendar), I created another measure using the techniques I learned during my research.  By turning the date into a numerical value through the use of rounding, I can create a very nice number which represents the distance of a given date in days from the selected date.

 

Relational Distance Safety = CALCULATE(ROUND(MAX('Safety(EX)'[Date]) - MAX('Frozen Calendar'[Date]),1), 
	FILTER(all('Safety(EX)'), 'Safety(EX)'[Date] <= MAX('Safety(EX)'[Date])))

This means using any date from any other table, I can now use my preferred lookup techniques to get the relative distance in days from the selected date by the user.

 

Closing:

I'm not the best at sharing this type of stuff.  Please ask questions and provide feedback so I can make this more easily understood.  How can I add an example pbix for people to see this in action?

 

EDIT:

SIMPLIFIED EXAMPLE AVAILABLE AT GITHUB

https://github.com/Syphontwo/PowerBIExamples/blob/master/exampleDateRange.pbix

5 REPLIES 5
Community Support Team
Community Support Team

Re: Relational Date Filter By Other Date (How To)

@MAwbre,

 

In addition, Show Categories With No Data might be used.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MAwbre Frequent Visitor
Frequent Visitor

Re: Relational Date Filter By Other Date (How To)

@v-chuncz-msft

 

I don't see an application for that in regards to a variable, relative date filter.  Could you please elaborate for my benefit?

Community Support Team
Community Support Team

Re: Relational Date Filter By Other Date (How To)

@MAwbre,

 

Suppose you only want to show certain days on the axis.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
theitguy Regular Visitor
Regular Visitor

Re: Relational Date Filter By Other Date (How To)

@MAwbre

 

I am really interested in your solution and I would really like to see the corresponding .pbix to see it in action. Can you share it maybe via dropbox or something else?

MAwbre Frequent Visitor
Frequent Visitor

Re: Relational Date Filter By Other Date (How To)

@theitguy I'll try to get some data cleaned up and sent over to you in a bit.

 

I have now added a sample PowerBI file to my Github available here

https://github.com/Syphontwo/PowerBIExamples/blob/master/exampleDateRange.pbix