Create Previous Month dynamic filter

[darko861]

Hi Guys,

I have been struggling with a file from the Goodly channel. I have incorporated his thinking concept into my own file, and everything works perfectly. My goal is to publish a similar file on services and create a working Dashboard. The Dashboard (tiles) goal is to always show the highlighted previous month for the current year. At the moment this won't work automatically when we enter June 2022, and I only want the month of May to be highlighted, I will have to update each tile manually. 

The attached file has two similar charts on two different pages (one chart uses calculation groups) but the charts have the same functionality. To highlight a current or previous month you will have to make a selection in the Month filter and it works fine. But let us say I want to make the month selection dynamic so it always shows the previous month selected based on which month is the current for the year (e.g, if we enter June then May should be highlighted automatically, and so on.) But remember the chart must show other data points from the current year but only one data point should be highlighted and that is the previous month.

The file:

https://www.dropbox.com/s/q7qsx5adenmonzt/Highlight%20Month%20in%20a%20Line%20Chart.pbix?dl=0

I have made some tests by creating some calculated columns in the date table and applying it to the pages in the report. But it still won't work when e.g. we are in June 2022 and I want May to be highlighted automatically, I will still need to make a manual selection based on the current setup I have so far. Is there a way to make the filter dynamic so it always selects the previous month?

[darko861 · Accepted solution]

I solved it by adding a new page to the report. There is no option to select which period you want to highlight in the filter. I wrote two new DAX measures which interact with the page filter and only highlight the previous month in the current year automatically. This page will only be dedicated to the Dashboard, so it updates the periods automatically. I will still keep the initial page in the report if the viewer wants to interact with the main report. 

Here are two new Dax measures:

Highlight = CALCULATE(Measures_T[Rank_sum],FILTER(DimDate,DimDate[Previous month]=1))

Value = 

Total_sales2_Line_chart = CALCULATE(Measures_T[Rank_sum],values(DimDate[Month Name]))

The previous month column is a calculated column I have added to my date table:

Previous month = IF(YEAR(TODAY())= YEAR(DimDate[Date]) && MONTH(TODAY())-1 = MONTH(DimDate[Date]),1,BLANK())

If you apply these steps then you will get the desired output of each tile in a Dashboard.

[speedramps]

Watch these videos about Calandar tables

Click here for videos 

Create a Calender with date and year offset and month offet (eg 0,-1, -2)

Use the the month offset  = 0 in your filter rather than May 2022.

Then it will refresh as required each month.

Please click thumbs up and accept as solution buttons. Thank you! 😀

[darko861]

Hi, this does not work for me because of the setup. I've tried your suggestion and it didn't work for me. If you look in the file (page: Highlight - without calculation groups), I have two lines:

CM Highlight :

and Total sales:

The Monthly Cal Table is a temporary date table with no relationships. In this scenario  the CM Highlightline is based on the selection you do in the Month filter:

So I think that the DAX code has to be amended as displayed above in order for this to work?