Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Hi @kattlees,
Try this formula:
Date_Diff = DATEDIFF(Table[Date],TODAY(),MONTH)
The month can be change to days, years, ....
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThat 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |