Power BI: Parameter Table and Dynamic Date Range

As we know Power BI is all about filter context, which brings us quite a lot convenience. But for some cases, it might not be what we want. For instance, what if I want to show amount of sales for last N months by a calendar month slicer? Because the slicer puts a constraint on the calendar month, you won't be able to show sales amount month by month. As illustrated below, how can we see total sales for 2012-12, 2012-11, etc.?

Normally what we can do is creating a disconnected date dimension, and using this disconnected date dimension for the slicer. But sometimes it could be very confusing to end users. So instead of creating a second date dimension, we can try to use date member from the fact table directly. So let's start today's topic:

Firstly the demonstration I built is from AdventureWorksDW2014.

To make it simple, I only loaded DimDate and FactInternetSales tables. Then, I removed default relationships and create the date relationship between FullDateAlternateKey and OrderDate columns directly.

Finally I created a custom column in the DimDate table for calendar month:

Calendar Month = FORMAT(DimDate[FullDateAlternateKey], "yyyy-MM")

Then we need a parameter table, which gives us control of how many months of sales user wants to see. In the real world scenario, we may need to create a junk dimension to hold these values. But in this demonstration, I just manually input the data.

Now let's create two simple measures:

Total Sales = SUM(FactInternetSales[SalesAmount])

Selected Period = IF(HASONEVALUE('Parameter Table'[Parameter Value]), VALUES('Parameter Table'[Parameter Value]))


If we follow normal steps to create visuals (create a slicer from 'DimDate'[Calendar Month], create a slicer from 'Parameter Table'[Parameter Name], then drop the table visual for calendar month and total sales), you will find the table visual won't show last N months sales, because the calendar month slicer puts a filter context to the fact table.

So let's make three changes to make it work:

1. add a calculate order month column into the fact table

Order Month = FORMAT(FactInternetSales[OrderDate], "yyyy-MM" )

2. add a measure for total sales of last N months. (note: Max function gives us the last date within the current filter context - the calendar month slicer value in this case)

Sales (in period) = CALCULATE([Total Sales], DATESINPERIOD(DimDate[FullDateAlternateKey], MAX(DimDate[FullDateAlternateKey]), -[Selected Period], MONTH))

3. now instead of using 'DimDate'[Calendar Month] and [Total Sales] for the table, we drag and drop 'FactInternetSales'[Order Month] and [Sales (in period)] into the table visual.

Easy job, isn't it? :D