Custom Year To Date in Tabular Model using DAX

The situation

Some time ago, when working on a pilot project for a client, I was presented with an interesting requirement for the SSAS Tabular Model + DAX, being a non-sequential way of looking at dates in the previous year. I was working on transactional sales data, which required some of the everyday hierachies (Date, Product, Geography, ..) and measures (Year to Date, Month to Date, ..). Things that are all brilliantly document by the SQLBI.com guys at DAX Patterns.

Based on those sales transactions I had to define time-related calculations on both a regular calendar, and a fiscal calendar. Nothing some trial-and-error, and a quick glance at the Time Patterns won’t solve. However, given the extra requirement of comparing the previous year in a non-sequential way, I had some thinking to do ..

Non-sequential way?!

First off, let me explain what comparing in a non-sequential way means. When comparing the previous year, usually a date gets compared to the same date in the previous year (ie. April 18, 2015 to April 18, 2014). In this situation it could be totally different, as shown in the image below.

Every month, the last day of that month gets compared to the first date of that month in the previous year, and pretty much all other dates are compared to (year – 1) + 1 day, unless there’s a holiday in that month. For this example I’ve chosen April, because the Easter holiday is in it, and it adds some complexity to the mapping. Sales transactions usually are a lot less on holidays, so it makes sense to compare those to each other.

Belgium (The small country known for waffles and beer) has an official holiday for both Easter, and Easter Monday, which means a lot of companies (and even more so for non-industry companies) don’t have office hours on those days, and a lot less sales transactions in the traditional way.

However, there was a slight mismatch on my provided data. I only had a date mapping available for 2014 and 2015, while my demo data pretty much stops at 2014. The mapping is done on a manual basis, and I altered it rather straight forward by deducting 2 years, rather than making it myself for those time periods. I pretty much created my own holidays, that’s how special I am!

The setup

Talking about mappings is all fun and games, but we all want to see the solution in the end. Talking about sales transactions, I couldn’t think of a setup any better than AdventureWorks to explain how I did it. For training and demo purposes I have access to an Azure VM, which allows me to do some experimenting.

One of those experiments is an SSAS Tabular Model based on the Internet Sales. I chose not to use the demo models, but create one myself to get some practice while I’m at it. An extra addition I made, is using my own date dimension over the standard AdventureWorksDW date dimension. It contains a lot, if not all, the same fields and some more, which help me perform DAX-calculations in an easier way. Personally I prefer expanding the date dimension with a few extra fields, over having to create extra complexity in the measures. For this specific case I added DayOfSale (OrderDate), and DayToCompare to the date dimension.

The solution

The first thing I had to take care of, was making sure the comparison to the previous year on the day level is done correctly. The pre defined time-intelligence are pretty much useless, as they require a sequential date table without gaps. Using the DAX-calculation, shown below, it worked like a charm! (Thanks, DAX Formatter!)

DAX_daymap_PY

With the parallel period lookalike out of the way, I still had to make sure that the ‘Previous Year to Date’ calculation respected the date mapping. I had some trial runs, in which correct date was used, but the running total wasn’t calculated in the correct order. This was mainly because I still took the regular total as a basis.

When I realised that I had to use the custom Previous Year calculation, I figured it out pretty quick, and I was able to perform a custom (Previous) Year to Date, on non-sequential dates. I’ve done some more experimenting, and by using this kind of calculation it’s possible to compare dates in multiple months, quarters, years, .. The image below shows the calculation that performs the Previous Year to Date in the correct way.

DAX_daymap_PYTD

Verifying that the results matched with the correct dates was very important, so I used my buddy Excel to get an overview.

DayMap_Result

The conclusion

This little snippet was particularly amusing for me, because it forced me to go beyond the basic time intelligence functions in DAX. I was pretty pleased with myself for solving this. A few days later I stumbled upon an article by Marco Russo on Custom Year-Over-Year calculation in DAX, which pretty much explains the same, I just take it a step further by calculating the Year To Date. I strongly encourage you to read the article, as it’s a real beauty.

This wraps up my official first blog entry, and I sure do hope you’ve enjoyed it! All comments are more than welcome, and I hope you’ll visit again 🙂

Hello World!

Welcome to my blog!

Pondering long and hard about starting to blog and thinking of lame excuses for not doing so, I finally decided to step up to the plate. I’ve been following the SQL Community for about 4 years now, and I’ve learned heaps by doing so. By blogging I hope to contribute the community, and maybe even inspire a few people.

Working as a Data Insights Consultant, I spend my days working with data, and using tools to make working with that data a lot easier. I pretty much work exclusively in the Microsoft Data Platform stack, which is expanding at an insane velocity. I’m tech savvy by nature, so I really enjoy trying to keep up with all the new shiny toys (Cortana Analytics Suite anyone?).

I’ll find inspiration for my topics in my day-to-day job, and any issues, oddities or challenges I encounter, but most of all about data related things I’m trying to grasp in my spare time.

Enjoy reading!