Display Last N Months & Selected Month using Single Date Dimension in Power BI

March 2, 2018
Share this...
Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Pin on PinterestShare on Reddit

It is Friday, and I thought of writing a quick tip on how you can use the same date dimension for displaying the last N months (say, in a bar chart) and the data for selected month (say, in a card visual). Most of the techniques for doing the same use a disconnected date dimension along with the regular date dimension; however, using the same date dimension as a filter has the additional benefit that you can use all of the other measures also without any changes in the same report.

imageBefore I show you the technique, let me show you an example of a finished report. Below, you can see that I have displayed 3 regular measures based on the selected month (Sales for selected month, Sales YTD and Sales Last Year) along with a bar chart that shows the sales for the last N months based on the selected month (special thanks to the folks at PowerBI.tips for the pretty layout).


Follow the steps below to recreate the same:-

1) For the purpose of this post, I am using a very simple model – a Sales table (with just Date and Sales) and a Date table.


2) Create 3 measures as shown below, and then add those 3 measures in the report along with a month slicer as shown below. You can change the month in the slicer and verify that the measure values change for the selected month.

Sales (Selected Month)
= SUM ( Sales[Sales] )

Sales Last Year
= CALCULATE ( SUM ( Sales[Sales] ), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) )

Sales YTD
= TOTALYTD ( SUM ( Sales[Sales] ), ‘Date'[Date] )




3) The next step is to make a measure that will display the last N months. Let us create a What If parameter called N with values from 1 to 24, and increments of 1. Place it in the chart as shown below


4) The main step for this technique is – create a measure that will display the sum of sales for the last N months. It is important to know that putting the Month from the Date table will not work, so what we are going to do is create a month column in the Sales table and then use that as the axis for the bar chart. Create the 2 calculated columns in the Sales table (MonthYear and also MonthYearNo for sorting the MonthYear column), as well as a measure – Sales (last n months).

= RELATED ( ‘Date'[MonthofYear] )

= RELATED ( ‘Date'[MonthYearNo] )

Sales (last n months) =
VAR MaxFactDate =
    CALCULATE ( MAX ( Sales[Date] ), ALL ( ‘Date’ ) ) — ignore the selected date filter, and find the max of date in Sales table
VAR FDate =
    ENDOFMONTH ( ‘Date'[Date] ) — get the last day of the month selected in the date filter
VAR Edate =
    EDATE ( FDate, – [N Value] ) — get the last day of -N months
    IF (
MaxFactDate <= MAX ( ‘Date'[Date] )
&& MaxFactDate > Edate,
        CALCULATE ( SUM ( Sales[Sales] ), ALL ( ‘Date’ ) )
    ) — if the date in the fact table is between the last N months, display Sales, else nothing. Note that we are ignoring the date filter, only respect the date in Fact

Update -3/3/2018

Owen Auger (twitter) has come up with an easier formula, use this one instead of mine –

Sales (last n months) =
    SUM ( Sales[Sales] ),
    DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – [N Value], MONTH )

5) Now create a bar chart with MonthYear on Axis and Sales (last n months) on the values, as shown below.


At this point, you can change the month in the filter, as well as the value of N and see the bar chart change (as well as the other measures). 

Posted by SQLJason


Nice technique using dates from fact table on the last n months visual.

One thing – I think this measure would give the same result:
Sales (last n months) =
SUM ( Sales[Sales] ),
DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – [N Value], MONTH )

Agreed, better and easier than mine. Post updated! 🙂

Hi Jason. Great article I was looking for this kind of solution for a long time. However I have a question regarding it’s mechanics.
As you wrote yourself this piece of code:
“VAR MaxFactDate =
CALCULATE ( MAX ( Sales[Date] ), ALL ( ‘Date’ ) ) — ignore the selected date filter, and find the max of date in Sales table”
ignores any filter on dates so basically it should always return the latest date in Sales Table. My question then is in which moment we’re getting some filtering on MaxFactDate so that this piece of code:
“MaxFactDate Edate”
as far as I understand reference to the MaxFactDate which will be somehow in this case equal to the date selected from slicer.
Could you please explain it a little bit so that I could use it more consciously
Thanks in advance

Owen has suggested an easier formula than mine. But if you were looking to understand the mechanics in my formula, MaxFactDate ignores the Date filter but respects the Sales[Date] filter. So in the chart, where we are having the Sales[Date] in the axis, it will always give the max(Sales[Date]). So at that point, you are just checking if the Sales[Date] on the axis is between the last n Months of Date[Date]. Hope that helps.

Leave a Reply