Calendar Over Run While Comparing Last Year’s Data

Recently, I was helping someone on Power BI community forum on the issue of calendar over run while comparing values with last year’s data. I thought it would make a good article to share with others.

The Problem

I will illustrate the issue using the AdventureWorks database (I need to make some tweaks to do this).  Assume that today is 5 June 2004, hence the actual sales exist up to 4 June 2004. I have filtered the load of AdventureWorks so it only loads data up to and including 4th Jun 2004.

Now consider the following two measures that I created in the Sales table.

YTD = TOTALYTD(SUM(Sales[ExtendedAmount]), 'Calendar'[Date])
YTD LY = CALCULATE([YTD], DATEADD('Calendar'[Date], -1, YEAR))

Next, I placed a Table in the report (I filtered the Table to show values only for years 2003 and 2004 to keep the list small).

As you can see above (#1), the Table is showing months into the future with no sales (months 7 – 12) while there are sales showing for YTD and YTD LY – this is what I call calendar over run.   Also, if we consider the values (#2 above) for the latest month (6, 2004) the value used to compare prior year is incorrect (it is comparing a few days in June 2004 with all days from June 2003).  Technically it is correct to load your calendar table through to the end of the latest full year, but this contributes to the above problem.  When YTD LY is calculated, it takes the current month (June 2004, which as a YTD formula includes all days from Jan-Jun in the filter context – 181 days) and gives you the result for the same 181 days in the prior year (Jan-Jun 2003).  But given we only have sales until 4th Jun this year, we are short by 26 days sales.   The result is that it compares 155 days this year with 181 days last year.

The Solution

There are various ways to solve this problem. One way to fix this issue is to add a calculated column to your calendar table that you can use in your page filter (or even the report filter if you like).

DateHasPassed = ‘Calendar'[Date] < TODAY()

In the case of my demo, I am using old data and hence I have replaced TODAY() with  DATE(2004, 6, 5) to simulate that date in history.

After the column is created, place the column DateHasPassed on the report page and set the filter to TRUE. Your Table should look like below.

As you can see above,

  • The calendar over run through to the end of the year has gone, and
  • YTD hasn’t changed, but YTD LY (#1) is now showing a lower number – the total up to and including 4th Jun 2003.  Note that the numbers are still quite different, but that is the nature of this sample database I am using.

You can see what is happening more clearly with a day view of the current month as shown below.

How Do You Solve This Issue?

I would be interested to hear how you solve this issue.  I note that the Italians put out a blog on this topic this week too.  Didn’t time that too well :-).

Share?

Comments

  1. I always trim my calendar. I’m with Rob Collie on this one, I don’t understand why you would want the calendar table to go beyond the date you have data. So usually, in Power Query I will usually trim the calendar table using dynamic calcs such as [date]<=Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(),-1))) if my reporting is always one month behind. You get the idea. This has the added benefit that you could write "current period" measures, such as Sales = TotalMTD('Sales'[Sales],'calendar'[date]) and it will default to the max month in the calendar table without the end user selecting a month (either in a Tabular cube or PBI report). This is especially useful for "snapshot" type data such as AR or Inventory balances.

Leave a Reply