Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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())
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
In addition, Show Categories With No Data might be used.
I don't see an application for that in regards to a variable, relative date filter. Could you please elaborate for my benefit?
Suppose you only want to show certain days on the axis.
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?
@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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.