The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all
I require some assistance with calculating the days difference in an employee capacity calculation. I understand that this topic has been covered in similar posts but I cannot seem to get it to work in my example. My data set includes employee information which includes an "Engagement Date" and "Termination date". I need to calculate for each employee their capacity over a given period (Lets say current financial year i.e from 01/03/2021 - Todays date). Extract of sample data in the table below:
I understand that their is a function in excel called "networkdays" that automatically calculates this for you where in PowerBi you have to use the function "datediff". I have managed to calc the capacity for all days though as per formula below:
This will indicate which days are work days by returning a 1 and a zero for weekends. The problem I have is that these two tables are not relatable in any way as my primary table has 3 different date conditions to determine capacity.
I am fairly new to PowerBi so would appreciate any assistance.
@shehzaad , If they are from same table , then you need a measure like
COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(max(Table[DateEngaged]),[TerminationDate]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
refer, with and without date table
How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA
with date table can be done, even if dates are across tables
Thanks, logic makes sense, however I seem to be getting an error for the [termination date] field and [date] field.
[Termination date] - This field does exist in my table and respresents a date field type. thought this would be the end date in this expression
[Date] - not sure what field to be inputting here. Is this the calculated date column in your example?
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
20 | |
15 | |
14 | |
9 | |
7 |