How do I filter a visual by the nth latest month?

[Loubot3000]

I have four graphs that I want to automatically update to always be filtered to the latest, second latest, third latest, and fourth latest months:

I have a central table containing UUIDs, dates formatted as mmmm yyyy (e.g. December 2023), and month number (increases by 1 each month infinitely since the beginning). This table is related to all my other data tables, including the data populating the above graphs, via UUIDs. I also have a separate, unrelated calander table that starts and ends on the same dates as the dates in my main table.

I've tried so many different variations of DAX measures to filter the visuals by, but none of them have worked so far. I also haven't been able to use the calander table to get this to work either.

Any ideas?

[Loubot3000 · Accepted solution]

Figured it out.

1. I made a bunch of measures to get the nth latest month number based on the previous (n-1th) latest month numbers. The Latest Month Number is just:

Latest Month Number = MAX('Sheet1'[Month Number])

And then the rest proceed from there. For example:

Fourth Latest Month Number = 
VAR ThirdLatestMonthNumber = [Third Latest Month Number]
RETURN
CALCULATE(
    MAX('Sheet1'[Month Number]),
    'Sheet1'[Month Number] < ThirdLatestMonthNumber
)

• Note that each one is filtered to be the max month number that is less than the n-1th latest month number.
• Also note that I made a variable at the top, outside the calculate function, to get the n-1th latest month number. This is because this measure needs to be calculated outside of the context of the calculate function for its value to be used in any subsequent calculations.

2. Then I used those to make new measures to do my data calculations filtered to each of those months. For example:

% FUNNEL (Fourth Latest Month) = 
VAR FourthLatestMonthNumber = [Fourth Latest Month Number]
RETURN
CALCULATE(
    [% FUNNEL],
    'Sheet1'[Month Number] = FourthLatestMonthNumber
)

•  Where [% FUNNEL] is my calculation - in this case, a weighted percentage of survey respondents answering a question.
• Note again that create a variable at the top to calculate the nth latest month number outside of the context of the RETURN calculation.

3. Then just use those measures in the graphs, and it will always update to the latest nth month, no manual filtering required 🙂

------------------------------------------ NOTES------------------------------------------------------

1. The nth Latest Month Number measures don't have to be based on the n-1th Latest Month Number measures. You can instead be more explicit and just do this for each one:

Fourth Latest Month Number = 
VAR LatestMonthNumber = [Latest Month Number]
RETURN
CALCULATE(
    LatestMonthNumber,
    'Sheet1'[Month Number] < LatestMonthNumber - 2
)

• Where the number subtracted in `LatestMonthNumber - 2` is adjusted for each one. In general it will by `LatestMonthNumber - n+2`. So in this case, for the 4th latest, it will be -4+2 = -2.

2. If using a date variable instead, something like @DataInsights' solution would probably also work.

[v-jiewu-msft]

Hi @Loubot3000 ,

If I understand correctly, the issue is that you want to filter a visual by the latest month. In order to make a deep troubleshooting, could you please provide more sample data(exclude sensitive data) with Text format or screenshot and provide the desired result? It would be helpful to find out the solution.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[DataInsights]

@Loubot3000,

Try this calculated column in your calendar table:

Relative Month = 
VAR vToday =
    TODAY ()
VAR vResult =
    DATEDIFF ( vToday, DimDate[Date], MONTH )
RETURN
    vResult

Create a relationship between your central (fact) table and calendar table. Then create a visual filter where Relative Month is between -3 and 0 (inclusive).