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

March 2, 2018

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).

Solution

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.

image

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] )

 

 

image

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

image


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).


MonthYear
= RELATED ( ‘Date'[MonthofYear] )

MonthYearNo
= 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
RETURN
    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) =
CALCULATE (
    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.

image

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

15 comments

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) =
CALCULATE (
SUM ( Sales[Sales] ),
DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – [N Value], MONTH )
)

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

The best and easiest way.

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
Tom

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.

This is great info. Can it be adapted to the following desired logic: based on a month selected in a slicer, calculate the sum for a three-month period starting 15 months ago and ending 12 months ago–something like:

mTotalSalesBetween15Mo&12MoAgo:=CALCULATE([mTotalSales], DATESBETWEEN(-15,-12, MONTH))

Hey Jason,

Thanks man. It saves lot of time.

@Owen Auger, Thank you for making it simple…

Hi Jason,

I tried this out and I am having issues with the arrangement of bar charts. The bar charts accurately depict the sales value for the respective month/year however the order is not correct. Do you have any idea what is wrong?

Jake Carville

Hi Jason,

I have a query that builds on from your guide and looks at including SAMEPERIODLASTYEAR() with the dynamic ‘X’ months selection.

Any help would be appreciated: http://community.powerbi.com/t5/Desktop/Show-sales-for-last-X-months-and-Same-Period-Last-Year/m-p/450197#M208415

vaishali pote

hi SQLJason,

your post was very helpful. i have one doubt that what is MonthOfYear and MonthYearNo? kindly revert

Hi,
I’ve been trying to follow your instructions along with the demo version, however I cannot get either of the below to work as it will not bring up the table/column to link to and gives me the error “The column ‘Date[MonthofYear]’ either doesn’t exist or doesn’t have a relationship to any table available in the current context.

MonthYear = RELATED ( ‘Date'[MonthofYear] )
MonthYearNo = RELATED ( ‘Date'[MonthYearNo] )

I have my sales table date and my dates table dates linked as a many-to-one relationship, as you have in the demo version.

Do you have any ideas on how to fix this please?

Best regards
Carrie

Joshua Keizer

There seems to 1 major flaw in this process. If your data is split into different areas, the following vulnerability arises.
Lets say you want to report sales by customer. This method will get the max date for each customer, meaning the Last 12 Month sales will be the last 12 months from when they stopped trading with us.
This is less an issue if youre looking at branches/divisions, however if they don’t generate the activity you’re monitoring (e.g. sales) every day of the week, then it will take last 12 months from their last sale, potentially artificially boosting their period numbers.
It also means that customers who stop trading with you will always show sales in the ‘last 12 months’ and never go away.

Abhishek Parihar

Hi SqlJason,
Excellent article Man …………….
I am also working with same scenario where I have to display sales based in Year. I don’t have any date column as such in my Model so I have to use Year column . Please suggest me if you can suggest me

Leave a Reply