One of the features that comes with the Power BI suite of analytics tools is DAX (Data Analysis Expression). DAX provides a wide range of functionalities for Trend Analysis such as YTD, MTD, QTD etc. Yet, there is no option to restrict the trend analysis to a certain period (For e.g. Last 8 quarters, Last 12 months etc.) with respect to the current date. This has to be done in such a way that the visuals keep updating dynamically in accordance to the current date (For e.g. Every quarter for Last 8 quarters trend or every month for Last 12 months trend).
Consider a scenario where we would like to show the Last 8 Quarter trend for Sales Analytics. If the current date is March 7th, 2017 which is in the First quarter of 2017 then the visual should show up values from the Second quarter of 2015 till the First quarter of 2017 summing up to 8 quarters altogether. Likewise if the current date is May 7th, 2017 which is in the Second quarter of 2017 then the visual should show up values from the Third quarter of 2015 till the Second quarter of 2017.
A simple solution to achieve this in Power BI is to maintain a separate table along with the index values representing the 8 quarters. Let us look into the detailed implementation of the solution.
Step 1: The first step is to create a separate table for all the quarters for the data present in the model and it has to be linked with the date table which contains all the date until the current date.
Step 2: The relationship between the tables are maintained as follows
Step 3: The next step is to create a measure which fetches the current quarter with respect to today’s date using DAX. This will return the index value from the Quarter table for the Current Quarter.
Step 4: Next create a calculated column to show up Quarter values if the index is between the Current Index and Current Index – 8 (For last 8 quarters) otherwise the values are to be left blank.
Step 5: Now create the visual with the Sales Order Value (say, Actual Sales QTD and Target Sales QTD) as the transaction data and Year-Quarter data as the dimension. In order to restrict the visual to last 8 quarters, add the Last 8 Quarter (Calculated column which was created in the previous step) in the visual level filter and set it to ‘IS NOT BLANK’.
The chart now shows up value only for the Last 8 Quarters and with just a manual refresh the chart updates automatically when the quarters change with current date as the data in the model view is updated with respect to Current Quarter Index.
The same can be done for Last 13 Months trend
By maintaining an index for the current period, trend analysis can be performed in Power BI with ease. This can be further extended by defining hierarchies for the displayed period such that the quarters drill down to their corresponding months or the months can drill down to their corresponding days to perform a more comprehensive trend analysis.
Want to know more? Feel free to reach out.
