Relative Date Filter Relative to Data
Relative Date Filter option is a great idea, however, not all Date data is current date and time. What if your data is for the period, for example, January 2016 to December 2016. I want to be able to set the date that the Relative Date should be relative to. The easiest solution would be for Power BI to grab the last date value from the data set (using this same example, maybe it would be 31 December 2016) and then use the same relative functions to apply to the data set. So if I wanted the last 3 months, it would be October, November and December 2016.
Kabir Laiwalla commented
The relative data filter is just working wonders. I love addition of the seven filters and makes it so easy for auto update and don't have to make changes each month. However we have current month "in this month", last month, "in the last = 1 calendar month" and we have current month last year, but that option is not available in relative data filer. It will be great that we can add "in this month" and the next filter is "in the last = 1 year" Looking forward to adding this soon. Or if we can enter -1 or in the last -13 months to show same month last year, etc.
David Markle commented
Also chiming in from a financial services firm. Yes, this functionality is key to us being able to do historical financial reporting in Power BI. Not having this is very tough and has us scrambling with limited success for workarounds.
Maarten Lauwaert commented
Adding the possiblity to add a measure which calculates the date from which the relative dates are computed would make this an excellent feature. Almost all financial reports are reported on a report date, not today, so showing the last 12m from report date (selected in the report) is necessary and very difficult to implement currently!
This is in particular something that you need to have in those scenarios when you have an approved period (true/false) flag on the dates. I most of the cases I work with, the companies has maybe the 2nd-8th workday in a month as their approvaldate. I need to "switch" over to right period when that date kick in, not before.
Definitely - using the max date rather than today is essential for any report with update frequency any lower than daily. A lot of our reports are weekly reports so we still need to use DAX in a custom columns to achieve a relative filter.
Alternatively, you could have multiple "Relevant Date" options:
- Latest possible date
- Earliest possible date
- Mid point date
- Custom date
The first 3 are self-explanatory, the customer date option would allow you to enter the date directly into the options for what date the filter should use as a relative date.