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

[Kiranbc]

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. 

[v-kkf-msft]

Hi @Kiranbc ,

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.

[Kiranbc]

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]

Hi @Kiranbc ,

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

[Kiranbc]

Yes, there is a active relationship between time entered date and date calendar. The hours (40 hours/week) should show only for that month rather than cumlative hours from 3/28 to till date. There is a column of employee entered hours, which is like more than one row per day. Let me know if you need more details

[v-kkf-msft · Accepted solution]

Hi @Kiranbc ,

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.

[Kiranbc]

Thank you so much Winniz. It worked. 

[Kiranbc]

Thanks,. Hours are working properly, now I have another issue. How to sum this up and use this in utilization calucation (Billable hours*100/total hours). I have summed up using sumx(

Measure 2 = SUMX(Utilization,[Measure])), but hours are showing weirdly because of time entered many rows per day. Even calculated column instead of measure has same issue. Refer below screen shot

[amitchandak]

@Kiranbc , Create a new column like

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

[Kiranbc]

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