Staff up to a specified date

[Consultant001]

Hi Team

I'm using this formula to count the sum of all Terminated staff up to a specified date.  However, I would like to change the measure so that it looks 1 year earlier from the selected date.

I think there needs to be a change made after “MAX<.......

I.e DATEADD(Table [Calendar],-1,YEAR)

But something that works.

Current formula

Thanks!

[Zubair_Muhammad · Accepted solution]

@Consultant001

May be this one

=
VAR maxdate =
    MAX ( Calendar[Date] )
VAR mydate =
    DATE ( YEAR ( maxdate ) - 1, MONTH ( maxdate ), DAY ( Maxdate ) )
RETURN
    CALCULATE (
        [count terminated],
        FILTER ( ALLSELECTED ( Calendar[Date] ), Calendar[Date] < mydate )
    )