Dynamic expected working hours and cost

[AllG]

Need help to calculate dynamic expected working hours and cost for the year, based on date selection and details as below:

Tables

• Emp Data – general info about employees (ID, Manager, Leader, BU etc.)
• Cost table – 2 columns, Emp ID and Hourly rates
• Date – Calculated table

Relationships

From:Emp Data[Emp ID] To:Cost[Emp ID] (1 to 1)

Expected hours = Workday * 8 for each employee, start date should be 2021/1/1 if joined before 2021/1/1 else start date and end date based on slicer selection

Expected Cost = Expected hours * Hourly Rate for each employee

[AllG]

I have achieved workdays calculation with help of below measures and it is working fine based on slicer changes, but not able exclude Weekend (Sat and Sun). any help will be highly apprecieated.

pbix file >> https://drive.google.com/file/d/1Bv5JZc9M6h0fqsB2iFWxALx-rS0fbgDV/view?usp=sharing 

.Start Date Check = 
VAR st =
    SUMX ( 'Emp Data', 'Emp Data'[Start Date] )
VAR yr_st =
    DATE ( 2021, 1, 1 )
RETURN
    IF ( st > yr_st, st, yr_st )

.End Date Check = 
VAR ter_dt =
    SUMX ( 'Emp Data', 'Emp Data'[Termination Date] )
VAR ed = [End Date]
RETURN
    IF ( ISBLANK ( ter_dt ) = TRUE (), ed, IF ( ter_dt < ed, ter_dt, ed ) )


WorkDays = 
SUMX (
    'Emp Data',
    DATEDIFF ( [.Start Date Check], [.End Date Check], DAY )
)
 

[v-lionel-msft]

Hi @AllG ,

", but not able exclude Weekend (Sat and Sun). any help will be highly apprecieated."

Please refer to the formula.

Measure = 
CALCULATE(
    DATEDIFF( [.Start Date Check], [.End Date Check], DAY ),
    FILTER(
        'Emp Data',
        NOT( WEEKDAY([.Start Date Check]) in {6,7} ) && NOT(WEEKDAY([.End Date Check]) in {6,7} )
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllG]

Hi Lionel,

Thanks for your reply.

I am looking for dynamic calculation of workdays, based on start date from "Emp Data" table and end date from slicer selection. And I am not getting correct results with above calculate measue, for example, January workdays showing as 30 and group by or summarize results are blank.

[AllG]

Monday to Friday are working days, holidays not require.

[lbendlin]

Please clarify what you mean by workday.  Which days are weekend in your country, and do you want to account for holidays?