Dynamic Monthly Filtering with Exception Handling

[Anonymous]

I'm facing a challenge in Power BI and would greatly appreciate your insights and suggestions to overcome it.

Here's my situation: I have a table with a date field and need to filter all the visuals in my report page by the current month. I've been using the filter page pane, dragging the date field, and utilizing relative date filters to achieve this. However, I've encountered an exception, when today's date is the 1st day of the month, there is no data available in my database for that day, resulting in empty visuals.

I'm seeking a solution that dynamically detects when it's the first day of the month. When this condition is met, I aim to display data for the previous month (e.g., if today is November 1st, I need to display data for the whole month, in this case, October). On other days, the filtering should simply show data for the current month (e.g., November).

I want this solution to be adaptable and change according to the current date.

Could you please share any ideas or techniques in Power BI that could help me address this scenario effectively? 

Thank you in advance for your support!

[AllisonKennedy]

@Anonymous  - does your date table have a 'month offset' column? You could calculate the month offset, but alter this slightly to allow the buffer for today. You'd need to add an IF statement to check if the Day = 1, then MonthOffset-1. 

https://community.fabric.microsoft.com/t5/Desktop/Month-Offset-rolling-12-months/td-p/3240050