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

25 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

Hi, I just tried this model, unfortunately the Months aren’t getting impacted by change in Parameters. Can you please help me?

Hi SqlJason

I was wondering if it would be possible to use the same tutorial with direct query. I’ve been trying it, but it has been imposible to show the data in the chart. I will be greatful if you can help me with it.

Thanks.

Hi I love this post, very simple solution for rolling values. I have an issue where I’m trying to apply the solution to a cumulative measure I have.
Cumulative measure:
BS LTD = CALCULATE ( [DrCr],
DATESBETWEEN (
Calendar[Date],
FIRSTDATE ( ALL ( Calendar[Date] ) ),
LASTDATE ( Calendar[Date] )
)
)

Rolling Measure:
BS Roll 12M = CALCULATE( [BS LTD], DATESINPERIOD( ‘Calendar'[Date], MAX( ‘Calendar'[Date]), -12, MONTH))

The issue I run into is that the measure now gets filtered out by the dates used in the calculated column on the table where the measure sits. Is there a way to do a rolling period for cumulative total?

MyMeasure = TotalLeaversYTD / NoOfPeople * (12 / n)
where “n” is the month for which the measure is being calculated
I have measures “TotalLeaversYTD” & ‘NoOfPeople’ which i am able to calculate accurately

I am unable to create a measure YTDAttrition which gets evaluated in the context of the selected month
I want the filtered month no to be considered as n
for e.g. while calculating YTD % as in May, the value of n should be 5-3 = 2 (2nd from Apr)
(Financial year considered as Apr to Mar)

https://community.powerbi.com/t5/Desktop/How-to-define-the-measure-which-uses-the-evaluation-context/m-p/529743#M248186

Hi, I really loved this and appreciate it. However, I wanted to show ‘same period last year(month) and current month’ comparison and am using Clustered column chart to display current month and same period last year bars. Suppose I choose February 2017 and the “n value” is -3. then the chart should show for Dec 2016(Dec 2015),Jan 2017(Jan 2016) and Feb 2017(Feb 2016) in the chronological order but its not happening.The months mentioned in the brackets are for same period last year. But the problem am facing here is sorting the x-axis. when i sort the x-axis according to month no, then the values on x-axis is displayed in this order- ” Jan 2017,Feb 2017 and Dec 2016″(since month no of the specified months are 1,2 and 12 respectively). Then i wrote a dax and created custom column to sort it according to Year&month. Year&month= (year)*100+monthno. then i sorted it according to the Year&month column. But here the sorting happens in this way.Dec 2015, Jan 2016,Feb 2016,Dec 2016,Jan 2017 and Feb 2017. I want it in this way to be sorted Dec 2016(Dec 2015),Jan 2017(Jan 2016) and Feb 2017(Feb 2016) in the chronological order.Can this be done?

I am using multiple years of data, it shows me the January 2016 data instead of September 2018 and August 2018.

Can it work with multiple years?

Hi SqlJason
Nice post, it worked really well!

I’m wondering if there is a way to show the cumulative sales during this N period, is it possible?

Thanks in advance!

Hi @Jason Thomas

Can you help me in achieving the MOM % trend.
I am using the trend of 13 months using your logic .(Creating the what if parameter).But, couldn’t able to get the MOM.

Sharing the sample PBIX file via one drive:

https://nablerin-my.sharepoint.com/personal/akhil_j_nabler_com/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fakhil_j_nabler_com%2FDocuments%2FLNRS%20Data%2FMOM%2Epbix&parent=%2Fpersonal%2Fakhil_j_nabler_com%2FDocuments%2FLNRS%20Data&slrid=32d1a59e-6010-7000-3bee-ff3004f06ea5

Hi Jason Thomas,

Can you help me in achieving the MOM % trend.
I am using the trend of 13 months using your logic .(Creating the what if parameter).But, couldn’t able to get the MOM.

Sharing the sample PBIX file via one drive:
https://nablerin-my.sharepoint.com/personal/akhil_j_nabler_com/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fakhil_j_nabler_com%2FDocuments%2FLNRS%20Data%2FMOM%2Epbix&parent=%2Fpersonal%2Fakhil_j_nabler_com%2FDocuments%2FLNRS%20Data&slrid=32d1a59e-6010-7000-3bee-ff3004f06ea5

How do you create the “N”? Seems like when I created with new columns has no response with the graph

Leave a Reply