Count number of months an employees has worked

[kattlees]

I need to count the number of months an employee has worked this year to date. We get paid biweekly. I figured if an employee got a check in a given month, it would count as 1, but I can't figure out the calculation.  Any help would be appreciated?

[MFelix]

Hi @kattlees,

Try this formula:

Date_Diff = DATEDIFF(Table[Date],TODAY(),MONTH)

The month can be change to days, years, ....

Regards,

MFelix

[kattlees]

Thanks for the reply MFelix, but if I put in HireDate and they have been here more than this year, it counts that too. I want just this year. Or if they leave during the year it should only count the months they were here.

[MFelix]

@kattlees

Try to use this: 

Months =
SWITCH (
    TRUE (),
    YEAR ( Hire[Hire_date] ) < YEAR ( TODAY () )
        && Hire[Leave_date] = BLANK (), DATEDIFF ( DATE ( YEAR ( TODAY () ), 1, 1 ), TODAY (), MONTH ),
    YEAR ( Hire[Leave_date] ) < YEAR ( TODAY () ), 0,
    YEAR ( Hire[Hire_date] ) < YEAR ( TODAY () )
        && YEAR ( Hire[Leave_date] ) = YEAR ( TODAY () ), DATEDIFF ( DATE ( YEAR ( TODAY () ), 1, 1 ), Hire[Leave_date], MONTH ),
    YEAR ( Hire[Hire_date] ) = YEAR ( TODAY () )
        && YEAR ( Hire[Leave_date] ) = YEAR ( TODAY () ), DATEDIFF ( Hire[Hire_date], Hire[Leave_date], MONTH ),
    0
)

Think I have all the options covered result is below:

Regards,

MFelix

[kattlees]

That works for months they have been employed. But I need to count only months they worked. We have employees who only work when needed. I need it to only count the months someone doesn't get a paycheck. 

So let's say Jane Smith works Jan, Feb, May, & June. She gets paid Jan. 15, Jan 31, Feb 15, Feb 28, May 28, June 15, She doesn't work the other months and not full months.  Her month calcuation would be 4 because she has worked in 4 calendar months.

It's not a straight DateDiff calculation. 

[MFelix]

Hi @kattlees,

Wasn't aware of that particularity, assuming you have a payment date for all employes even those who full time and the payment is a column so you can have several lines for the same employee add the following:

Add a column with the formula:

Payment_Month = MONTH(Payment[Payment_date])

Then add a measure with the formula:

Months Worked =
CALCULATE (
    DISTINCTCOUNT ( Payment[Payment_Month] ),
    YEAR ( Payment[Payment_date] ) = YEAR ( TODAY () )
)

Then just add the ID of employee and Months worked measure to your table and you get the result, as you can see the first two dates are from 2016 and aren't considered for total of worker 1.

Regards,

MFelix