TIME COMPARISON DAX MEASURES IN POWER BI
As stated in my previous article, the stakeholders requested for a year-on-year and month-on-month financial performance of the Office Sales. Time Comparison DAX Measures such as Year-on -Year, Month-on -Month performance, Revenue Growth, Profit Growth etc. are important metrics for calculating financial data in businesses. The first and mot important thing to consider when dealing with time intelligence is to create a CALENDAR TABLE.
While working on the Office Sales Dataset, I was constantly getting a lot of errors because, I used the Date Column in the Financial Table. Not until, I created a new Date Table and further created a one to many relationships between the two tables (Financial and Date Table).
TIME COMPARISON FUNCTIONS
While working on the year-on-year and month-month performance, I discovered three different DAX function which returns the same result but, can be applied in different contexts. You need to understand the filter context or syntax and what period they work with. These DAX functions are
· PARALLELPERIOD()
· SAMEPERIODLASTYEAR() and
· DATEADD()
First thing first, create a measure to calculate the total amount of revenue or profit, we’ll work with the total amount of revenue for the sake of this article.
PARALLELPERIOD()
Returns a parallel period of dates by the given set of dates and specified interval. This means it returns strictly a table of values which is dependent on the number of intervals and interval.
Syntax
ParallelPeriod (Dates, No of Intervals, intervals)
This means that the result will be the total revenue of the past year. The no of intervals may very depend on what your stakeholders prefers (-1 means the year was moved backwards by one).
SAMEPERIODLASTYEAR()
Returns a set of dates in the current selection from the previous year.
Syntax
SamePeriodLastYear (Dates)
This returns the same result, the total previous year’s revenue.
DATEADD()
I would recommend using the DateAdd function when dealing with time comparison. This returns a table that contains column of numbers shifted either forward or backwards in time by the specified number of intervals in the current context. This is somewhat similar to ParallelPeriod but, the difference will be discussed shortly.
Syntax
DateAdd (Dates, No of Interval, interval)
RESULTS
All three returned the exact values for the total past year’s revenue.
- What could be their differences? Let’s switch this up a little.
2. What happens when we include month in date column?
PS: From the dataset, 2013 only recorded the revenue of the last quarter, which is why January to August is empty.
DIFFERENCE BETWEEN PARALELPERIOD, SAMEPERIODLASTYEAR AND DATEADD
Their difference is their filter context. The ParallelPeriod works statically on the interval (Year). SamePeriodLastYear changes irrespective of the change in period same applies to DateAdd.
Which leaves us with the difference between SamePeriodLastYear and DateAdd.
SamePeriodLastYear only moves a year back while DateAdd two or more years back.
CONCLUSION
They may seem to return the same result but, note that understanding the Filter Context for these functions is key.
