cancel
Showing results for
Did you mean:
Helper I

## How to find the consecutive number of last months in which your Employee is inactive

I have the following data.

 Emp Code Month Allocation Overtime 1 Nov-22 1 1 1 Oct-22 1 0 1 Sep-22 1 1 1 Aug-22 1 1 1 Jul-22 0 0 1 Jun-22 1 0 1 May-22 0 0 2 Nov-22 0 0 2 Oct-22 0 1 2 Sep-22 0 0 2 Aug-22 1 1 2 Jul-22 1 0 2 Jun-22 1 0 2 May-22 0 0 3 Nov-22 0 0 3 Oct-22 0 0 3 Sep-22 0 0 3 Aug-22 0 0 3 Jul-22 0 0 3 Jun-22 0 0 3 May-22 0 0 4 Nov-22 0 0 4 Oct-22 0 0 4 Sep-22 0 0 4 Aug-22 0 0

What I need to know that for how many last consecutive months has the employees being inactive. Criteria of being inactive is their allocation + Overtime is 0.

So, my expected result would be something similar to this,

 Emp No Months Inactive 1 0 2 1 (has overtime = 1, in 2nd last month) 3 7 4 4

I made something like this as a measure to calculate the Inactive Months, but this is giving me wrong results,

``````Months Inactive =
MONTH(TODAY()) -
MAXX(
FILTER(
Employee,
Employee[Allocation] = 0 && Employee[Overtime] = 0
),
MONTH(Employee[Month])
)``````

Results I am getting with this code is the following,

Can anyone help me out on this?

1 ACCEPTED SOLUTION
Solution Sage

Here is a measure expression that shows one way to do it.

``````Months Inactive =
VAR vMaxDate =
MAX ( Employee[Month] )
VAR vMinDate =
MIN ( Employee[Month] )
VAR vLastActive =
CALCULATE (
MAX ( Employee[Month] ),
OR ( Employee[Allocation] = 1, Employee[Overtime] = 1 )
)
VAR vResult =
IF (
ISBLANK ( vLastActive ),
DATEDIFF ( vMinDate, vMaxDate, MONTH ) + 1,
DATEDIFF ( vLastActive, vMaxDate, MONTH )
)
RETURN
vResult``````

Pat

Microsoft Employee
Solution Sage

Here is a measure expression that shows one way to do it.

``````Months Inactive =
VAR vMaxDate =
MAX ( Employee[Month] )
VAR vMinDate =
MIN ( Employee[Month] )
VAR vLastActive =
CALCULATE (
MAX ( Employee[Month] ),
OR ( Employee[Allocation] = 1, Employee[Overtime] = 1 )
)
VAR vResult =
IF (
ISBLANK ( vLastActive ),
DATEDIFF ( vMinDate, vMaxDate, MONTH ) + 1,
DATEDIFF ( vLastActive, vMaxDate, MONTH )
)
RETURN
vResult``````

Pat

Microsoft Employee

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors