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
