Working days difference between 2 dates for an employee capacity calculation

[shehzaad]

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:

Capacity = if('Employee Details'[TerminationDate]<date(2021,03,01),0,if('Employee Details'[DateEngaged]<date(2021,03,01),DATEDIFF(date(2021,03,01),TODAY(),DAY),if('Employee Details'[DateEngaged]>date(2021,03,01),DATEDIFF('Employee Details'[DateEngaged],TODAY(),DAY)))).

Now reading up on some support posts, I understand that a new calendar table may need to be created to differentiate between work days and weekends (lets ignore public holidays for now), which has been demonstrated below:

Is work day = SWITCH(WEEKDAY('Calender Table'[Date]),1,0,7,0,1)

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.

[amitchandak]

@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

[shehzaad]

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?