How to add “previous year” filter to the measure

[mroshchenko]

Hello everyone.

I have an “Opening Staff” measure, which shows the number of employees at the beginning of each calendar year. I need to add a “previous year” filter to this measure to get a number of employees at the beginning of the previous year. Adding the filer “previous year” (YEAR ( TODAY () ) – 1) from the Date column to the visuals does not work for my goal, because I need separate measures for future calculations.

Original measure, which works well

Opening staff =

VAR BeginningOfPeriod = MIN('Date'[Date])

RETURN

CALCULATE (

DISTINCTCOUNT (EM[Employee]), EM[Type] in {"A"},

FILTER (EM, EM[HireDate] < BeginningOfPeriod && (

EM[TerminationDate] >= BeginningOfPeriod

|| ISBLANK ( EM[TerminationDate]  ))))

I tried to add PREVIOUSYEAR filter, but the measure still shows number for all years

Opening staff Prev Year=

VAR BeginningOfPeriod = MIN('Date'[Date])

Return CALCULATE (

    DISTINCTCOUNT ( EM[Employee] ), EM[Type] in {"A"},

    FILTER(FILTER(EM,

            EM[HireDate] < BeginningOfPeriod && (

             EM[TerminationDate] >= BeginningOfPeriod

             || ISBLANK ( EM[TerminationDate] ))),

 PREVIOUSYEAR('Date'[Year])))

This does not work

Opening staff Prev Year=

VAR BeginningOfPeriod = MIN('Date'[Date])

VAR PrevYear = YEAR ( TODAY () ) – 1

VAR OpenStaff = CALCULATE (

    DISTINCTCOUNT ( EM[Employee] ), EM[Type] in {"A"},

    FILTER (EM, EM[HireDate] < BeginningOfPeriod && (

    EM[TerminationDate] >= BeginningOfPeriod

    || ISBLANK ( EM[TerminationDate]  ))))

RETURN

  OpenStaff in {PrevYear}

Please help.

Thank you.

[mroshchenko · Accepted solution]

The previous year condition has been achieved by

VAR BeginningOfPeriodPrevYear = MIN('Date'[Date]) - 365

[Ashish_Mathur]

Hi,

Does this work?

=calculate([opening staff],previousyear(calendar[date]))

[mroshchenko]

calculate([opening staff],previousyear(calendar[date])) - does not show the error, but it is empty in the visual, does not present any number. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.