Forum Discussion

AllG's avatar
AllG
Frequent Visitor
5 years ago

Dynamic expected working hours and cost

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

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

  • AllG's avatar
    AllG
    Frequent Visitor
    5 years ago

    Monday to Friday are working days, holidays not require.

  • AllG's avatar
    AllG
    Frequent Visitor
    5 years ago

    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's avatar
      v-lionel-msft
      Community Support
      5 years ago

      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's avatar
        AllG
        Frequent Visitor
        5 years ago

        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.