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 e...
Need Help
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.