cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper V

## 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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors