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.
For running example of this post you will need AdventureWorksDW sample database, or you can download Excel version of it from here:
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;
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;
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 the Power BI file of demo from here: