Power BI: Create a Custom Look Back Range

Custom Week/Month Look Back Range Based on User-Selected Date

This tutorial shows you how to create a custom date range “look-back” slicer that is relative to another date that has already been selected in another slicer. I.e., a dynamic relative date slicer.

Power BI Scenario:

The client wanted a Power BI report in which the user could select a given time-period (day/week/month) and not only see aggregations for the selected period, but also allow the user to customize how far back the report looks (i.e., last 4 weeks, last 3 months, last 6 months, last 2 years, etc.) This tutorial shows you how to create a custom drop-down range of look-back dates that will filter the Sales and other metrics based on the selection.

Assumptions:

You first need to start with at least a data table and a Date table, and these tables need to be related on a date field. In this demo, we have an Orders table, and we will be creating a Sales measure based on the user-selected date range.

Tutorial:

We will create our first slicer using a field from the Date In this demo, I have used “Month-Year” as the time-period selector, but you can use any time-period you wish. This could be a single day, week, month, year, etc.
To create the MonthYear (and MonthYearNo) calculated columns in the Date table, use the following formulas:
1. MonthYear = FORMAT('Dates'[Date], "mmmm-yyyy"
2. MonthYearNo = YEAR('Dates'[Date])&FORMAT('Dates'[Date],"MM")
You must create MonthYearNo in order to properly sort the MonthYear column. Once you have created both of these columns, select the MonthYear column header, and in the ribbon select Modeling →Sort by Column →Now the MonthYear column will be properly sorted in the slicer, once you add it.

To create your custom look-back range, you will create a new table in which you will list out the names of the dates ranges and the number values of the ranges.
1. Click Home in the ribbon, then click Enter Data and a Create Table wizard will appear.
2. In this demo, I called the table “LookBack”, and I made the following two columns:
  1. “Range Label” (what the user will see in the slicer dropdown)
  2. “Number” (the month equivalent of the label)
3. NOTE: You do not have to do these same date ranges. You do not even have to do Months. You could do days, weeks, quarters, or years. Note that if you chose to do weeks, you will need to translate the number of weeks into days (i.e. # weeks * 7) for the Number column, because Power BI does not let you select WEEK as a date part.
4. Load the table. Now you will see it appear in your Fields pane.

Next you must create a measure which detects what value has been selected in the LookBack dropdown. I called this measure “SelectedLookBackRange”
1. Create a new measure in the LookBack table you just created.
  1. SelectedLookBackRange = SELECTEDVALUE(LookBack[Number], 24)
2. NOTE: The last argument in the formula above (24) is your Alternate Result number (this is what this measure is set to if nothing is selected from the dropdown). Please make sure this fits your case scenario.
Next you must create dynamic metrics that are calculated based off your SelectedLookBackRange. In this example we will be making a dynamic version of the Sales metric. My original Sales measure looks like this:
1. Sales = SUMX(Orders,Orders[Sales])
2. Now we must create our dynamic sales measure. The formula looks like this:
  1. Sales_dynamic =
    CALCULATE ([Sales], DATESINPERIOD ('Dates'[Date], MAX('Dates'[Date]), - [SelectedLookBackRange], MONTH))
  2. Note how I use the SelectedLookBackRange measure in the formula above in order to calculate how far back the sales should aggregate. In this demo, I use the MONTH date part, but it also gives you the option of DAY, QUARTER, and YEAR.

My field pane now looks like so:
Repeat step 6 for every metric in which you would like to be dynamic (ex.: Units Sold, Average Retail Price, etc.)
Now we are ready to add the slicers to the canvas and double check that they work.
In the canvas, make two slicers:
1. “MonthYear” from Dates table (or your preferred field from the Date table)
2. “Range Label” from the LookBack table
Finally, I created a card in which I placed the “Sales_dynamic” measure.
Play around with the two slicers and double check against your data that the dynamic sales metric is correct.
If all you needed the dropdown for was for cumulative sales, then you are done! If, however, you wanted to view all of the selected months broken out, then we will need to take an additional step.
To view all of the selected months on the canvas, we cannot use the MonthYear column in the Date table, because we have only selected a single time period in the Date table (in this demo – December 2015), so if we were to drop MonthYear from the Date table into a table on the canvas, we would only see December-2015 listed. We would not see the past 6 months listed.
In order to see all the months broken out, we need to actually add the same MonthYear and MonthYearNo columns to our data table (Orders in this demo).
1. Click on your data table (Orders) and create the following two calculated columns:
  1. MonthYear = RELATED ( 'Dates'[MonthYear] )
  2. MonthYearNo = RELATED ( 'Dates'[MonthYearNo] )
2. Remember to sort your MonthYear column by the MonthYearNo column so that they will be listed in chronological order in the table visual. Repeat what you did in step 3 of this tutorial to Sort by MonthYearNo.
Now on the canvas, create a table and drop in the new MonthYear column you just created in your data table and the Sales_dynamic measure. Now you should see the months listed out.
Continue to adjust the slicers and ensure you are seeing expected behavior. Enjoy!