Use max slicer value to filter another table

[brian0782]

Hi

I have a slicer where I'd like to use the max value to filter another table:

Year of Account = FILTER((AccountYear), VALUE(AccountYear[yoa_code]) <= CALCULATE(MAX(Period[period_year])))

However the table is not being filtered - I'm expecting 2024 to appear as 2020:

Hard-coding the value gives me the desired result:

Can someone advise wher I'm going wrong please?

Thanks

Brian

[brian0782]

Hi @Pragati11 

I have data up to 2019 but want to show all columns upto the Period Year in my table - even if there is no data. If I hard-code the value 2020 into the Year of Account, I get the desired result:

Also @Pragati11, Year of Account is a calculated table:

[brian0782]

Hi @Pragati11 

I've created the following measure:

Max Period Year = CALCULATE(MAX(Period[period_year]), ALL(Period))

and used it here:

Year of Account = FILTER((AccountYear), VALUE(AccountYear[yoa_code]) <= Period[Max Period Year])

However, I'm still seeing 2024 rather than 2020:

[Pragati11]

Hi @brian0782 ,

What all date range data you have in your data?

Is that you have data till 2024 but want to show only till 2020 in the slicer?

Also in your slicer you have got a different field moved. See below:

You should move the new calculation in your slicer "Year of Account".

[Pragati11]

Hi @brian0782 ,

Create a measure for your maximum period year as follows:

Max Period Year = CALCULATE(MAX(Period[period_year]), ALL(Period))

Now use this measure in place of "CALCULATE(MAX(Period[period_year]))" in your DAX expression for Year of Account.

Let me know if this works.

Thanks,

Pragati