In this article we will demonstrate how to display last n months data in chart based on selected month in slicer. Means, user will select any month from slicer and chart will show last n month’s data including selected month.
For example, user selects Nov 2014 from slicer and want to see last 5 month’s data than chart will show last 5 months including Nov 2014. You can see the desired output in below screenshot.
Lets see the data tables, here we have two tables named financials and dates.
financials table includes sales data while Dates is a dimension table which includes a range of dates starting from year 2013 to year 2014.
There is a one to many relationship between dates and financials tables.
Lets create a bar chart which displays total sales by months and years.
You can see on X-axis, we have taken a MonthYear field from financials table and Total Sales on Y-axis.
Field MonthYear is a calculated column while Total Sales is a measure as shown below.
MonthYear = FORMAT(financials[Date],"MMM-YYYY")
Total Sales = SUMX ( Â financials, Â ( financials[Sale Price] * financials[Units Sold] ) - financials[Discounts] )
Now, create one dates slicer to select the last month, for this we need to create a calculated column named MonthYear in date table as shown below.
MonthYear = FORMAT(Dates[Date],"MMM-YYYY")
Now, we need to create one more calculated column named YearMonth to sort the month’s order in slicer.
YearMonth = FORMAT(Dates[Date],"YYYYMM")
Now, create one slicer that will allow user to select last n values means how many last month’s data you want to see.
Lets create a what if parameter for this as shown below.
Go to Modeling tab, then expand the New Parameter tab then select the Numeric range.
Now a pop up window named Parameters opens, now provide the details accordingly and click on Create button as shown below.
You can see, we have taken minimum values as 1 and maximum values as 12. So, It will add a slicers on report page having values starting from 1 to 12 as shown below.
You can also see, it creates a table named Last N Months in your data model.
Lets format the slicer and make it a single select drop down list.
Also, lets make MonthYear slicer a single select slicer. Just go to slicer settings and checked Selection as Single select.
So far, we have created one bar chart which display total sales by months, one months slicer and one last n months slicers which shows 1 to 12 numeric values.
Now, we will create a measure which will show the last n month’s totals sales on bar chart dynamically based on user selected month and last n values in slicers.
Lets create a measure named Last N months:
Last N Months = CALCULATE( Â [Total Sales], DATESINPERIOD(Dates[Date], MAX(Dates[Date]), - [Last N Months Value], MONTH) )
Now, drag the measure Last N Months into chart’s Y-axis.
Lets see the last 5 months sales for that just select Nov 2014 from MonthYear slicer and 5 from Last N Months slicer.
You can see, it shows the last 5 months sales from selected month that is Nov 2014.
Lets create the dynamic Title for chart, for this we need to create a measure as shown below.
Title = "Sales of last " & [Last N Months Value] & " months"
Also Read..
Hello!! If I create a measure with dates in period and try to display this in a bar chart in powerbi I only get one bar for the elected month and not the previous month, any idea why this is happening?