Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Please help in getting flat weekly 40 hours based on joining date.

Please help in getting flat weekly 40 hours based on joining date for all the resources through dax. The time entry started from 28Mar2022. There people joined after that and hours should be pro data basis. There are multiple entries for a single day. 

 

 

 

Need Help
Tips and Tricks
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please try the measure.

     

    Measure = 
COUNTROWS (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MAX ( MAX ( 'Table'[Hire Date] ), MIN ( 'Calendar'[Date] ) )
            && [Weekday] <= 5
    )
) * 8

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

9 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Create a new column like

    countrows(filter(addcolumns(calendar(max([Hiredate], date(2022,03,28)) ,today()), "WorkDay", weekday([Date],2)),[WorkDay]>6))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit, Thanks for that but I am getting I am gettting values 1-12. I think it is not working..Please check the screen shot

       

       

       

       

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Do you want to calculate the hours worked so far? Please create a separate calendar table, then add the following calculated column.

     

    Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), TODAY () ),
    "Weekday", WEEKDAY ( [Date], 2 )
)

     

    Weekly hours = 
COUNTROWS (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MAX ( 'Table'[Hire Date], dt"2022-3-28" )
            && [Weekday] <= 5
    )
) * 8

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Weekly hours are not getting properly. For example I am selecting april month and weekly hours should show 21 (working days) *8 = 168 , but it is showing sum of all months. I  have selected dont summarize.

       

       

      • v-kkf-msft's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        Is a relationship created between the Calendar table and the Hire table?

        Do we need to calculate the hours worked for the selected month or the hours worked from 3/28 (or date of hire) to today?

         

        Best Regards,
        Winniz