Dynamically Show Completed Months in Power BI

One common requirement I have come across a few times is to limit a Power BI visual to only show completed months and exclude the current active month. This is common if you load data on a daily basis, but some of your reporting is done on a monthly basis. The chart below is a good example.

This chart shows the monthly sales of books from my online shop over the last 10 months. It looks like the sales have fallen off a cliff in August 2019, but that is not the case. This chart was generated early in the month of August and hence it shows a partially completed month. Probably the best way to make this chart reflect reality is to simply filter out the current (non-completed) month – August 2019 in this case. But the trick is to do this dynamically so the chart will still automatically update once the full month is complete.

As usual in Power BI there are various ways to solve this problem. The way I like to do it is to create a calculated column in the calendar table (you could use Power Query too). There are multiple ways to create a calculated column – but here is my solution.

Is this a Completed Month?

I solve this problem by creating a column called IsCompletedMonth. My column returns TRUE() for completed months and FALSE() for the current and any future months. I generally advise against using calculated columns and suggest you use measures instead. The exceptions to that rule include:

• When you want to slice, dice and filter on the result (you can’t do this with measures).
• When you are adding the column to a lookup (dimension) table (lookup tables are generally smaller and hence an extra column has less impact).
• When the column has a low number of unique values (low cardinality columns compress very well, even in large tables).

In this case I satisfy all of those exception rules listed above, so we are good to go.

Prepare The Calendar Table First

I think one good practice in a calendar table is to have a month index column. The month index is an integer column that uniquely identifies every month in your calendar table. The integer column should start at 1 for the first month loaded, 2 for the second month, 12 for the last month at the end of the first year and then continue on 13, 14 …24 at the end of the second year and so on. The benefit of doing this is that you can do mathematical calculations in order to find relative months, such as – 1 to find the previous month, – 12 to find the same month prior year and so on.

Once you load your calendar table with a month index column you’re able to use a combination of the TODAY() function and also LOOKUPVALUE() in order to find out what month index is the current active month.

Completed Month = 
      VAR ThisMonthID = 
              LOOKUPVALUE('Calendar'[Month Index], 'Calendar'[Date], TODAY())
      RETURN ThisMonthID

LOOKUPVALUE() is a lot like VLOOKUP() in Excel, but the syntax is in reverse.

Excel VLOOKUP(<Find This Value>,
              <In This Column In Another Table>,
              <And Return The Value From This Column>
      )

DAX LOOKUPVALUE(<Return This Value From This Column in Another Table>,
                <By Searching This Column In Another Table>,
                <And This Is The Value To Search For>
      )

The benefit of LOOKUPVALUE() over say RELATED() in DAX is that you don’t need a relationship. All relationships come at a cost in Power BI. You should only create relationships in Power BI when you want to propagate filters from one table to another. If you just want to fetch values from another table into a second table, then use LOOKUPVALUE() instead and skip the relationship.

At this writing, the current date is 10^th Aug 2019, and as you can see below in my calendar table, the Month Index for Aug-19 is the value 50.

And my new (partially written) calculated column [Completed Month] returns 50 for every row in the calendar table. Note how I have only partially written this formula and returned the value of the variable to “see” that this part is working as desired.

Now that I have used LOOKUPVALUE() to find the Month Index for today’s date, I can compare this against the month index column for every date in the calendar table to work out if each date is in a completed month or not. Here is the modified formula.

Completed Month = 
      VAR ThisMonthID = 
              LOOKUPVALUE('Calendar'[Month Index], 'Calendar'[Date], TODAY())
      RETURN 'Calendar'[Month Index] < ThisMonthID 
          //returns TRUE() if the month is in the past and complete

And here is the resulting column in my calendar table (below). Note that all dates in the current month (Aug-19) are FALSE, and all dates from Jul-19 and older are showing TRUE.

Now I have this column, I can use it as a page or visual level filter in my workbook. The chart is no longer misleadingly showing me that my sales have plummeted in August.

When I refresh my report in September, the calculated column will update and the August sales will automatically be visible.

How Do You Filter Your Completed Months?

Do you use a different approach? Let me know how you filter your completed months in your Power BI reports in the comments below.