Previous Dynamic Period DAX Calculation

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-01-12_11h08_12

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:


Enter Your Email to download the file (required)

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

 

2017-01-12_10h29_40

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

2017-01-12_10h31_14

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)

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:

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.

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;

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

2017-01-12_10h38_35 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() 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() used here to fetch first value only.

Move one day back

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:

End of Previous Period

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

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;

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

2017-01-12_10h55_35With 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;

Showing all of these in a page now;

2017-01-12_11h08_12

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:


Enter Your Email to download the file (required)

Save

Save

Save

Save

Save

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad

Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.


2 Comments

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *