Relative date filtering and delayed month-end

Relative date filtering is a great way to filter your data while keeping the current date in context. I’m sure you may have used this feature many times before and find it very valuable – but it doesn’t always meet business requirements, especially when the relative date filtering is based on when the previous month-end process is completed. We usually see this in the Finance department when they close the books for the previous month in the subsequent month. This can take 3-5 business days – and in some cases, even more time.

Learn more about relative date filtering here.

In this blog post, I’m going to talk about how to work with relative date filtering based on when the month end-process is completed. For example, imagine a simple bar graph showing the last 12 months of sales data. In this bar graph, we are using relative date filtering, meaning that when we are in January 2020, the bar graph shows sales from January 2019 to December 2019. If we are in February 2020, sales are shown from February 2019 to January 2020 – so based on the current month, the last 12 months are always being shown. This can easily be achieved by using the in-built relative date filtering functionality. This is an awesome feature, but unfortunately it doesn’t work when we have to use relative date filtering based on when the month-end processing is completed.

Here is a problem statement posted on the Power BI Community forum:

I am a big fan of the Relative Date Slicer in Power BI.  Financially – we report monthly.  The majority of my reports use a Last 36 Months (Calendar) – and this works great.  As I write this – my slice is 3/1/2016 – 2/28/2019.  Just what I want. 

My problem is – that window will change with the calendar.   On the first of April – the dates the slicer uses will be 4/1/2016 – 3/31/2019.  We have a processing cycle that takes about three or four business days to complete.   So the calendar may say it is April – but that does not imply we are done with March.  We typically are not ready for this switch until the 6th or 7th.

I can write a DAX function that would know the last certified date.  I can also put a certified attribute on my Date dimension. 

Does anyone have any ideas on how I can delay the transition?

In the above post, the certified date is a trigger that tells us when the previous month-end processing has been completed; this will help us calculate relative date filtering for our visuals.

Now let’s talk about how I approached this solution. The first thing to understand is how critical the Calendar table is for our solution. As a best practice, one must always have a Calendar table in their data model.

There are some great resources out there that talk about how to add a Calendar table to your model. I’m not going to discuss it here, but it is a very important part of our solution.

Here is what our sample data looks like:

There is also a Month End table which has a single Date column. This has a record for each month when the month-end processing is completed.

To calculate our relative date filtering, we are going to add a column to our Calendar table which we will call Month Offset. If the Month Offset value is 0, then that is the most recent month based on the most recent processing date. Negative values mean past months and positive values mean future months.

Month Offset =
VAR __monthEndDate = MAX( 'Month End'[Date] )  --get most recent processing date
VAR __date = IF( ISBLANK( __monthEndDate ), TODAY(), __monthEndDate ) --if there is no date then we use today's date
RETURN
DATEDIFF ( __date, 'Calendar'[Date], MONTH ) --calculate the offset value

Assuming the most recent month-end processing date is December 2019, then our Month Offset data will look like this:

To showcase this functionality, I created a power query parameter to change the most recent month-end processing date. In the video below, you can see how the bar graph changes based on this date:

Learn more about power query parameters here.

I have been able to use this technique to meet various financial reporting needs – what are some other techniques that you enjoy using?