Parallel Period is a function that help you fetching previous period of a Month, Quarter, or Year. However if you have a dynamic range of date, and you want to find the previous period of that dynamic selection, then Parallel Period can’t give you the answer. As an example; if user selected a date range from 1st of May 2008 to 25th of November 2008, the previous period should be calculated based on number of days between these two dates which is 208 days, and based on that previous period will be from 5th of October 2007 to 30th of April 2008. The ability to do such calculation is useful for reports that user want to compare the value of current period with whatever period it was before this. In this post I’ll show you an easy method for doing this calculation, I will be using one measure for each step to help you understand the process easier. If you like to learn more about DAX and Power BI, read Power BI online book from Rookie to Rock Star.

Prerequisite

For running example of this post you will need AdventureWorksDW sample database, or you can download Excel version of it from here:

Current Period

I will go through this with an example; Create a new Power BI Desktop file and choose DimDate, and FactInternetSales from AdventureWorksDW. Make sure that there is only one Active relationship between these two tables based on OrderDateKey in the FactInternetSales table and DateKey in the DimDate table. Now add a slicer for FullDateAlternateKey in the page

Also add a Card visual which shows SalesAmount from FactInternetSales table.

I normally prefer to create an explicit measure for this type of calculations, that’s why I have create a measure named “This Period Sales” with DAX code below; (the measure for This Period Sales is not necessary, because Power BI does the same calculation automatically for you)

This Period Sales = SUM(FactResellerSales[SalesAmount])

Start of Current Period

To understand the current period, an easy way can be calculating start, end of period and number of days between these two. Start of Period is simple. I just create a measure under DimDate, as below:

Start of This Period = FIRSTDATE(DimDate[FullDateAlternateKey])

FirstDate() DAX function returns the first available date in the current evaluation context, which will be whatever filtered in the date range.

End of Current Period

Same as start of period, for end of period I will use a simple calculation, but this time with LastDate() to find the latest date in the current selection.

End of This Period = LASTDATE(DimDate[FullDateAlternateKey])

Days in This Period

Next easy step is understanding number of days between start and end of period, which is simply by using DateDiff() DAX function as below;

Days in This Period = DATEDIFF([Start of This Period],[End of This Period],DAY)

I add them all in the report as Card Visuals (one for each measure), and here is the result so far;

Previous Period

After finding number of days in this period, start, and end of current period, it is a simple calculation to find the previous period. Previous period calculation should be number of days in this period minus start of current period. to exclude the start of period to calculate twice, I’ll move one more day back. Here is the calculation step by step, I’ll start with Start of Previous Period;

Start of Previous Period

Using DateAdd to reduce number of days from DimDate

DATEADD(DimDate[FullDateAlternateKey],-1*[Days in This Period],DAY)

DateAdd() DAX function adds a number of intervals to a date set. In this example interval is DAY, and date set is all dates in DimDate[FullDateAlternateKey] field (because DateAdd doesn’t work with single date), and the number of intervals is Days in This Period multiplied by -1 (to move dates backwards rather than forward).

Fetch the First Date of the result

FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],-1*[Days in This Period],DAY))

FirstDate() used here to fetch first value only.

Move one day back

PREVIOUSDAY(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],-1*[Days in This Period],DAY)))

To exclude current date from the selection we always move one day back, that’s what PreviousDay() DAX function does. it always returns a day before the input date.

These are not three separate DAX expressions or measure, this is only one measure which I explained step by step. here is the full expression:

Start of Previous Period = PREVIOUSDAY(FIRSTDATE(DATEADD(DimDate[FullDateAlternateKey],-1*[Days in This Period],DAY)))

End of Previous Period

Similar to the Start of Previous Period calculation, this calculation is exactly the same the only difference is using LastDate();

End of Previous Period = PREVIOUSDAY(LASTDATE(DATEADD(DimDate[FullDateAlternateKey],-1*[Days in This Period],DAY)))

Days in Previous Period

You don’t need to create this measure, I have only created this to do a sanity check to see do I have same number of days in this period compared with previous period or not;

Days in Previous Period = DATEDIFF([Start of Previous Period],[End of Previous Period],DAY)

Now if I add all of these measure to the report with card visuals again I can see previous period calculation works correctly;

With every change you apply in date range slicer you can see the previous period calculates the range again, it will be always same number of days as the current period, but same number of days BEFORE. in the screenshot above you can see that start of previous period is 321 days before start of this period (1 more days because the end of previous period is not exactly start of this period, it is one day before. we don’t want to duplicate values of date in current and previous calculations).

Previous Period Sales

Now as an example I have created another measure to show you the sum of SalesAmount for the previous period. the calculation here uses DatesBetween() DAX function to fetch all the dates between start of previous period and end of previous period;

Previous Period Sales = CALCULATE(SUM(FactResellerSales[SalesAmount])
	                             ,DATESBETWEEN(
									 DimDate[FullDateAlternateKey],
									 [Start of Previous Period],
									 [End of Previous Period]),
									 ALL(DimDate) )

Showing all of these in a page now;

Summary

This was a very quick and simple post to show you a useful DAX calculation to find Dynamic Previous Period based on the selection of date range in Power BI report page. I have used number of DAX functions such as FirstDate(), LastDate(), DateAdd(), DateDiff(), and PreviousDate() to do calculations. Calculation logic is just counting number of days in the current period and reducing it from the start and end of the current period to find previous period.

Download

Download the Power BI file of demo from here:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.