Slicer filtering affects one colum

[Osmelbrito]

I have a table that show cummulative values for a general ledger report

and I have a slice filter above of this report to select month accumulated to

is it possible to specify that filter applied in slicer only affect to current year and not to all columns in table?

this is the formula I used for cummulative amount

Importe_Acum = format(calculate(sum(REP_MAYOR_GENERAL_BCE[IMPORTE_AMT])+0, FILTER(ALL(FECHA[Date]),FECHA[Date]<= max(FECHA[Date]))),"#,0;(#,0)")

[v-yalanwu-msft · Accepted solution]

Hi, @Osmelbrito ;

You could modify the measure  as follows:

Importe_Acum = 
VAR _last =
    FORMAT (
        CALCULATE (
            SUM ( [Value] ) + 0,
            FILTER ( ALL ( 'Table' ), [Date] <= MAX ( [Date] ) )
        ),
        "#,0;(#, 0)"
    )
RETURN
    IF (
        YEAR ( MAX ( [Date] ) ) < YEAR ( MAXX ( ALL ( 'Table' ), [Date] ) ),
        _last,
        CALCULATE (
            SUM ( [Value] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[COL], 'Table'[Cuenta ] ),
                YEAR ( [Date] ) < YEAR ( MAX ( [Date] ) )) )
            + CALCULATE (
                SUM ( [Value] ),
                FILTER ( ALLSELECTED ( 'Table' ), YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) ) )))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please define "current year" - based on filter context this probably won't help you.  But if you mean "2021"  then you can create a measure accordingly that would honor the slicer for the other years but not for 2021.

[Osmelbrito]

When I mean current year, according to image is 2021, but, this report, next year, will show in the last column 2022, so I need to the filter to be dynamic

[lbendlin]

That is one aspect.  But what you also need to consider is that in your sample visual all years are "Current Year" in the context of their column data.  So you probably want to replace "Current Year"  with "Maximum Year we have fact data for"