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:

Hi Reza,

Thanks for this useful post.

Actually, I have another suggestion – tell me what you think about it.

– [Total Sales] = SUM(FactResellerSales[SalesAmount])

– [DaysInterv] = DATEDIFF( MIN ( Calendrier[Date] ) ; MAX( Calendrier[Date] ) ;DAY )

– [Previous Period Sales] = CALCULATE( [Total Sales] ; DATESINPERIOD(Calendrier[Date];MIN(Calendrier[Date])-1;- [DaysInterv]- 1;DAY) )

Cheers,

Tristan

Hi Tristan,

Thanks for your suggestion.

DatesInPeriod is also good function to use, they produce same result. The method I have mentioned is only one of many ways of doing this.

Cheers

Reza

I use this a lot. Thank you. I am running into trouble when I have more data and additional relationships set up with the date key in the date table. It’s not giving me all the dates.

Can you please share your PBIX file with me?

Cheers

Reza

This is officially my favourite blog post of the month. Many thanks for sharing this cool powerbi work around.Great that you shared all the working as well.

๐๐

Hi Reza,

Thank you for sharing your knowledge. I’ve been reading your articles all day long since last week.

Thanks ๐

Hi Reza,

You said at the beginning: “normally prefer to create an explicit measure for this type of calculations, thatโs why I have create a measure named โThis Period Salesโ “. And you suggested the formula:

“Sales = SUM(FactResellerSales[SalesAmount])” instead of “Sales = SUM(FactInternetSales[SalesAmount])”

Didn’t you call the wrong table?

I might have used the wrong measure name ๐ but the tale name looks alright to me ๐