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.

  2. Hi Matt, I just realized that the new column on the Calendar table as you wrote in your post should better be a True – False column.
    So the last step should be:
    #”Added Custom” = Table.AddColumn(#”Inserted Month”, “Date2”, each if [Date] <= Date.From(EndDate([Date])) then true else false)

    • Matt, my previous comment was not posted, I don’t know why.
      Matt, the way I tried to solve this issue was to follow your approach of adding a new Date2 column to my Calendar table and the approach that ‘delswingle’ suggested on the following link,
      https://community.powerbi.com/t5/Desktop/Power-Query-M-version-of-CALENDARAUTO-DAX-function/td-p/53747
      but to get the End Date (by creating a Power Query function) from the column ‘OrderDate’ from the table ‘FactInternetSales’ that is in the AdventureWorksDW2014 database.

      //***Function End Date***
      (EndtDate) =>
      let
      Source = FactInternetSales,
      EndtDate = Table.Group(Source, {}, {{“MaxDate”, each List.Max([OrderDate]), type datetime}})[MaxDate]{0}
      in
      EndtDate

      //Calendar Power Query query to create the Calendar table and the Date2 column (the last step in the following query) to later
      //filter my Power Pivot Table up to the last date from the FactInternetSales table.
      let
      Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
      #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}),
      #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),
      StartDate = #date(2013, 1, 1),
      Today = DateTime.Date(DateTime.LocalNow()),
      Length = Duration.Days(Today – StartDate),
      Custom1 = #”Changed Type”,
      #”Added Custom Column” = Table.AddColumn(Custom1, “Año-Mes”, each Text.Combine({Date.ToText([Date], “yyyy”), “-“, Date.ToText([Date], “MM”)}), type text),
      #”Added Custom Column1″ = Table.AddColumn(#”Added Custom Column”, “Mes”, each Date.ToText([Date], “MMM”), type text),
      #”Inserted Year” = Table.AddColumn(#”Added Custom Column1″, “Año”, each Date.Year([Date]), Int64.Type),
      #”Inserted Month” = Table.AddColumn(#”Inserted Year”, “MesNo”, each Date.Month([Date]), Int64.Type),

      //The following Step adds a new column with Dates up to the last date that is on the FactInternetSales table.
      //I drag this column to the Filters area in Power Pivot in Excel and deselect Blank to filter up to the last date
      // from FactInternetSales.
      #”Added Custom” = Table.AddColumn(#”Inserted Month”, “Date2”, each if [Date] < = Date.From(EndDate([Date])) then [Date] else null) in #"Added Custom" If you want your Calendar table to start from the first date from the FactInternetSales and to end up to the last date from your fact table, you need the StartDate function and the following PQ query: //***Function Start Date*** (StartDate) =>
      let
      Source = FactInternetSales,
      StartDate = Table.Group(Source, {}, {{“MinDate”, each List.Min([OrderDate]), type datetime}})[MinDate]{0}
      in
      StartDate

      //***Calendar Table***
      let
      Start = Number.From(StartDate(null)),
      End = Number.From(EndDate(null)),
      Source = List.DateTimes(StartDate(null), End – Start + 1, #duration(1, 0, 0, 0)),
      #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}})
      in
      #”Renamed Columns”

  3. EndofYear = DATE(Calendar_Current[Year],12,31)
    //do not use ENDOFYEAR in case your dates do not go to the year end as it returns the last date in your calendar!!!

Leave a Reply