Recently at a training I was asked how to implement DAX Time Series functions like parallel period and YTD in PowerBI. I always assumed that I had something written up about it but on closer inspection found that I have missed writing about such an important piece of functionality. So here is a step by step explanation on how to use some of the most commonly used Time Series functions in PowerBI.
First you need a date column in the original dataset. For example the below screenshot shows a table which contains data for flights in the US, the important column that I must have is highlighted in the box. Notice that the datatype is shown as datetime, however I actually only have dates within the column all times are defaulted to 12:00:00.000.
Next I need a calendar table created which meets the below criteria:-
- Must have dates that cover the range in the dataset. E.g. I usually like to have a calendar with ten years’ worth of dates in it.
- Must have continuous range of dates with no gaps. Even if there are no flights on Feb 25th in the original dataset the calendar table must have date corresponding with 25th Feb
- The Calendar table must be related to the Report data table using a relationship as shown below in PowerBIDesktop.
Next we need to help identify the calendar table within PowerBI, which is done by navigating to the table within the PowerPivot mode of PowerBI Desktop and clicking the Mark as DateTable button on the ribbon for Modelling
Once done we are ready to create new measures for the Calculation of ParallelPeriod and YTD
Here are the formula for your reference.
TOTALYTD
YTDFlights = TOTALYTD(SUM(ReportDataset[Flights]),FlightDates[flightdate])
The blue part is the name of the measure which we are creating it can be called anything you like
The black and bold part is the name of the Formula
The red part is the calculation we want to perform for the Year Till Date. In this case I want to calculate the Sum of all flights taking off this the current date starting from the beginning of the year.
The Green part is the way the PowerBI accesses the calendar to know when the year starts and how to navigate the calendar.
PARALLELPERIOD
LastMonthFlights = CALCULATE(sum(ReportDataset[Flights]),PARALLELPERIOD(FlightDates[flightdate],1,MONTH))
This formula is pretty self-explanatory , literally translated it is ” Calculate the sum of flights from the reportdata set for a period parallel to today by looking at the calendar table and stepping forward by one month.”
When used within PowerView the result would look like below after dragging and dropping the fields from the Field List on the right. One thing to pay attention here is that the month column must be fetched from the calendar table even if you may have a month column in the Original dataset too.
Please Consider Subscribing
