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:

Sample Sales Data

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:

Month Offset Column Value

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?

Dynamic title/measure based on what is NOT chosen in a #powerbi Slicer

A few months ago, a very interesting question was posted on the Power BI community forum and I had the opportunity to work on it. Finally, I have the time to write a blog post about this so that we can all learn from it!

Before we start, here is the question that was asked:

“I’ve seen several posts on how to display the values chosen in a slicer… but what about displaying the values that weren’t chosen (de-selected) – i.e. “All Choices Except _____”  My problem is that I have some slicers with 20+ choices in them, and they need to be able to multi-select. I could create a (verrrrry loooong) title that lists everything that was chosen, but even if you make the display big enough to see all of them, the really crucial piece of information is still obscured – namely, what was intentionally left out.

Example – you have a chart that displays the age demographics of people who eat fruit. There are only 5 age groups displayed in the graph, but there are potentially 25 different types of fruits. The fruits are in a slicer. Lets say you want to see the graph with all fruits except Grapefruits. So you go to your slicer select all choices and then de-select Grapefruit.

The normal dynamic slicer using CONCATENATEX is going to display the 24 fruits chosen, but unless you knew all 25 choices off the top of your head, you won’t notice that the one that’s missing is Grapefruit. So I’d like a measure capable of showing both the normal list of what was chosen in the slicer, BUT… if the number of choices selected is (TotalChoices – 1), then show “All Except ” and the one choice that was excluded.  Even better, if I could allow 2 choices for the exclusion logic (“All Except Grapefruit, Raspberry”).

Is there a way to identify choices that have been de-selected? Any alternative ideas for ways to approach this?”

The sample data is just a list of Fruits and Vendor:

Sample data

The request in the original post is to show list of not selected fruits (All except) if the total selected fruits are less than 1 or 2 of total available fruits. In other words, if 10 fruit values are available in the slicer, and let’s say the user selects 8 fruits then you want to show “All Except” – otherwise you want to show all the selected fruits. I made this option a bit more dynamic using a what-if parameter with the range from 0% – 100%, where you can set after what % you show not selected fruits (All except) – otherwise “selected” fruits are shown. Taking the previous example, if we have 10 fruit values available in the slicer, and the what-if parameter value is set to 90% this means that after 9 fruits are selected we show “All Except”. In case the what-if value is 80%, we show “All Except” after 8 values are selected and so on…

Here is a measure to achieve all this:

Slicer Values Selected =
VAR __selectedValues = VALUES ( Fruits[Fruit] )
VAR __totalValues = CALCULATETABLE ( VALUES ( Fruits[Fruit] ), ALL ( Fruits[Fruit] ) )
VAR __totalRows = COUNTROWS ( __totalValues )
VAR __selectedRows = COUNTROWS ( __selectedValues )
VAR __differenceRows = __totalRows - __selectedRows
VAR __perecentageSelected = DIVIDE ( __selectedRows, __totalRows )
RETURN
SWITCH( TRUE(),
    __totalRows = __selectedRows || __selectedRows == BLANK(), "All values selected",
    __perecentageSelected <= [Maximum Selected Value], CONCATENATEX( __selectedValues, Fruits[Fruit], ", " ),
    "Selected except " & CONCATENATEX( EXCEPT ( __totalValues, __selectedValues ), Fruits[Fruit], ", " )
)

And here is the breakup of the above measure:

__selectedValues = Create a table of selected fruits
__totalValues = Create a table of all the possible fruits available
__totalRows = Count of total fruits table
__selectedRows = Count of total fruits selected
__differeceRows = total rows – selected rows
__percentageSelected = What is the % of selected fruits after which we want to show “All except”

In the final return (switch) statement we are checking :

  • If the total and selected rows are equal, or if no rows are selected then “All values are selected.”
  • If the percentage of selected values are less than or equal to the what-if parameter percentage values, then we show all selected values.
  • Otherwise, just show “All Except.” In other words, don’t show the selected values.
Download

Hope you find this post useful and can use this technique to provide valuable information to the report users.

Download Sample PBIX

Useful Links

Learn more about CONCATENATEX function here.