Let’s really go deep into DAX formula with the Power BI PARALLELPERIOD function. We’ll look into this amazing DAX function and see how to use this in quite an effective way inside some calculations. You may watch the full video of this tutorial at the bottom of this blog.
You might also find some other usages different to what I’m going to demonstrate in this DAX function review. I’m going to walk through the logic and show you how it works.
How PARALLELPERIOD Works
PARALLELPERIOD is a time intelligence function. So what it does is it goes back to a time window that we specify, and then does a full calculation of that particular time window.
In this sample calculation, we’re looking back one year. PARALLELPERIOD inside this calculation is jumping back one year, then looking at that entire year and summing up that entire year’s sales.
We drag this into our table and we see the results on every row. This $11,565,545 is the Total Sales in 2016, while the $11,690,763 is the Total Sales in 2015, which is set here as the Prior Sales.
If we look at 2017, the $11,565,545 Total Sales of 2016 becomes our Prior Sales.
We can also do this by quarter. We simply change Year in the formula to Quarter and we have a slightly different result.
Another example is that we can also go “plus” (+) instead of “minus” (-). So we could go plus quarter or plus year.
From here, we could also do some simple measure branching and work out some percentages of totals.
Working Out Percentages of Totals
We bring in our Cumulative Sales to calculate the percentages of our totals. We’re going to find out, through this year, how we go based on our last year’s results. This is quite an interesting analysis from a percentage perspective.
That’s exactly what I’ve done here. The formula is not difficult at all. We’re simply implementing measure branching effectively. We branch out into this additional logic.
And so if we drag this into our table, it adjusts or changes through time and we can see that we ended up 103% over what we were last year.
We can also show this in a visualization.
***** Related Links*****
The DATEADD Function: The Best And Most Versatile Time Intelligence Function In Power BI
Calculate Financial Year To Date (FYTD) Sales In Power BI Using DAX
Time Comparison For Non Standard Date Tables In Power BI
Conclusion
Here I showed you a perfect example of when and how to use the Power BI PARALLELPERIOD. I also demonstrated how we can branch out into more calculations from our core measures.
The key concept of how PARALLELPERIOD works is that it opens up a time window based on what we specify. It goes and looks back in any particular context and returns that entire year’s or quarter’s or month’s result.
The great thing about this DAX function is how easy it makes us do this dynamically using filter functions. There would be a lot more to it. Maybe a slightly longer formula, but it’s a great time intelligence function.
Explore more on this function and apply it to your work. Cheers!
Sam
