How to lock all previous data in a chart from slicer except the most recent

[noname_nobody]

I'm a beginner with PBI and I'm trying to figure out how to solve my problem. I have data for each day. I'm making a chart where I want to have a count for each year. But the last year (in this case 2023) should be set using slicers. The others should be locked to the last quarter and should not be changed using slicers.

This is how it works now:

It works correctly for quarter 4, but not for quarter 3 because it shows all years in a particular 3th quarter as expected.

I have tried many solutions, for example to create two charts where the first chart shows the history of all previous years, the second only shows the last year. But you need to use multiple slicers to work with that.

So my question is whether I can somehow lock the previous years and leave them only for the 4th quarter? This seems like the easiest option, but I can't find any similar problem to the one I'm having. I will be glad for any other simple solution where I can just use these two slicers and be able to solve it.

[Ritaf1983 · Accepted solution]

Hi @noname_nobody 
You can manipulate the filters that you are applying with dax.
Assume that your model includes the calendar table you can use something like :

Wanted measure = IF (

    SELECTEDVALUE ( 'Calendar'[Year] )

        = CALCULATE (

            YEAR ( MAX ( 'orders'[Order Date] ) ),

            ALLSELECTED ( 'Calendar'[Year] )

        ),

    [total_sales],

    CALCULATE (

        [total_sales],

        QUARTER ( 'orders'[Order Date] ) = 4,

        ALL ( 'Calendar'[Quarter] )

    )

)

Result :

Pbix is attached
More information about the date table is here :

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Calculate function :

https://www.youtube.com/watch?v=Vz-38fJ4asc

All and allselected :

https://www.youtube.com/watch?v=6t5D7K8jZvc

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[noname_nobody]

Hello, thank you for your answer.
But I still have a problem.

I use this measure:

Wanted measure =

IF (

    SELECTEDVALUE ( 'Date table'[Year] )

        = CALCULATE (

            YEAR ( MAX ( Values[Date_Valid] ) ),

            ALLSELECTED ( 'Date table'[Year] )

        ),

    SUMX ( Values, Values[sum_values] ),

    CALCULATE (

        SUMX ( Values, Values[sum_values] ),

        QUARTER ( Values[Date_Valid] ) = 4,

        ALL ( 'Date table'[Quarter] )

    )

)

But in my graph, this works only for last quarter. For others it looks like this

The orange column is important - it should have the same values as the dark blue columns for 2020-2022 and for 2023 it should have the same value as the light blue value - which is correct.