How to display the last 13 months of data in a Power BI bar chart using only Year and Month slicers?

[RobWM39]

First, let me explain how I structured this report. Please review the picture below as you read through the description.
I have a dataset of waste pick-ups and need to create a bar chart in Power BI with weight (Tons) on the Y-axis and date (Month) on the X-axis.

Report description
My report currently uses three date slicers:

• Top left corner: Year slicer and Month slicer controlling the visuals (indicated by green arrows).

• Bottom right corner: A between-style date slicer that only affects the bar chart above it (indicated by a red arrow).
  
  I want to simplify my report by using only the Year and Month slicers, but still display the monthly weights from Dec 2022 to Dec 2023 as shown in the picture below. My goal is to show the last 13 months of data dynamically, ending at the month selected in the Month slicer.
  
  What I thought would work is connect my value table with a full Calendar table and then use 'Relative Date' option on the filter pane to filter the data for the last 13 months dynamically. Turns out there isn't an option for me to choose the point where I want the filter to count from so It automatically counts from the date that has the most recent data.
  
  I tried connecting my dataset to a full Calendar table and using the 'Relative Date' option in the filter pane, but it only counts from the most recent data, which doesn't allow me to dynamically set the end month.

  How can I configure my bar chart to display the last 13 months of data based on the selected Month slicer without using the third slicer?

  

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached file.

[RobWM39]

Thank you so much, Ashish! However, I still have an issue where some months display 14 months of data, with the oldest month showing only the last entry of that month. For example, in the picture below, I have selected 'February' in the dropdown filter, but the chart displays data from Jan 2022 to Feb 2023. I don't understand why January 2022 appears with 1.99 tons (the value of the last entry for that month in my spreadsheet).

I'm sure I did exactly like the file you've shared. And I don't see anything wrong with my dataset in the spreadsheet. What do you think cause this issue? 

[Ashish_Mathur]

You are welcome.  I do not know the casue.  It works perfectly in my file.