Forum Discussion
Advanced Relative Date Filter to Include Current Month and Months to come up
Ran into an issue using the Out the Box Relative Date Page/Report filter in Power BI.
Problem:
The Relative Date Filter when using the options "Is in the Last" or "Is in the Next" X Number of months does not include the entire month even when including today. It will only include today's date and the X Months,
Solution:
I created an IF statement that would rank the months based on the current month = 0 and assigning negative numbers for prior months and positive numbers for yet to come Months.
MonthRank = IF(SIGN(MONTH([Date]) - MONTH(TODAY())) = -1,5,MONTH([Date]) - MONTH(TODAY()))
In my case i needed to just see anything equal to the current month +4 Months and anything else negative I didnt want to have show up. So i needed to give negative numbers a number higher than my filter to allow for those dates to not show.
Then I used the filter Advanced filtering to filter with the number range I needed. This is dynamic enough where you will not need to change the filter each month as it will evaluate based on the current month.