How to use a variable to select the 6 most recent months of data from single date slicer

[djallarii]

Hello Power BI Community,

I am having trouble applying variable measure logic to my current project. I know it's possible, but the tutorials I have found typically apply the variable measure to some sort of calculation like 6 Months Total Sales when I need the entire matrix updated.

What I want to do is select a single value from a date slicer and return the most recent 6 months of data in a matrix.

Using the linked project as an example, if I select 2023-03 from the Year Month slicer, I would like my matrix to show data for only 2022-10 to 2023-03.

Link to project from OneDrive https://1drv.ms/u/s!AoXwZT-xo7jRgfRlbPfxBJiS0L0Ung

[djallarii]

Interesting approach! I believe this is exactly what I am looking for and I appreciate you taking the time to help me out on this one.

One thing I noticed, however, is that for a SelectedDate with an EOM number that is less than the EOM number from the 6th prior month, it was pulling in an additional record.

For example, February ends in a 28 and the 6th prior month, August, ends in 31. So, selecting February 28, 2023 will return a SixMonthsAgo result of August 28, 2022 which added an extra record to the matrix.

Another example, November ends in 30 and the 6th prior month, May, ends in 31. So, selecting November 30, 2023 will return a SixMonthsAgo result of May 30, 2022 which also added an extra record to the matrix.

To account for this, I added an EOMONTH to the SixMonthAgo variable:

VAR SixMonthsAgo = EOMONTH(EDATE(SelectedDate, -6),0)

Really awesome job!

[MKeppel · Accepted solution]

Please see attache file.

I created a disconnected Date table to be used fro the filter. Added the EOM property on both date tables.

Created a measure, that returns 1 if the eom is between the -6 months of the selected monthfilter.

Added that measure to the TOP N on the EOM filter on the matrix.

https://docs.mobius.kiwi/f/09559d7aabf543678ac8/