Dynamic % of hours worked by Calendar measure

[ctedesco3307]

I need to report a % of billable hours worked by weeks, months, depending on how many hours the resource is available. Most people are hours  day but we have some part timers

In my Employee table I have the Hours per day they are expected to work

https://docs.google.com/spreadsheets/d/1WUMJc5DcaRbUhH08spNdVfFz8g4A5l7H1SvKsL1ITJs/edit#gid=0

In my worklogs table I have what they have entered as billable hours for each project they work on

https://docs.google.com/spreadsheets/d/1Uword5KlJ7WJJZPJ_agdodsViQbdkJAvEdSaF5fab_w/edit#gid=0

In my calendar table I have the date breakdowns

https://docs.google.com/spreadsheets/d/1Quq6mlax15D2KXwQpYQl0KzDlzlGYNXD/edit?usp=sharing&ouid=11101...

 The report time dimensions will change – day, week, month, etc so I need the calc to be dynamic

All help appreciated – thanks in advance

[ctedesco3307]

Thanks @v-cazheng-msft  - I'm assuming the 5 means the amount of of working days in the week? Is there a way to make that dynamic where The calculation can SUM the amout of working days in whatever time interval I am looking at? I want to work with a date heirarchy that will drill down from Year, Quarter, Month, Week, Day So for example in the snapshot you have below, anyone who is 40 hours a week with at WrkHours value of 8 in the Employee table shoould show 100% If they are 32 hours a week with a  WrkHours value of 6.5 in the Employee table they should still show 100% 

[v-cazheng-msft · Accepted solution]

Hi @ctedesco3307 

Please try this solution.

1 Create a Measure to calculate workdays

Workdays =

CALCULATE (

    COUNT ( 'calendar'[WeekDay] ),

    ALLSELECTED ( 'calendar' ),

    FILTER ( 'calendar', 'calendar'[WeekDay] < 6 )

)

2 Create a Measure to calculate workhours

WorkHours =

SUMX (

    SUMMARIZE ( worklogs, worklogs[AUTHOR_NAME], worklogs[RelatedWrkHrs] ),

    worklogs[RelatedWrkHrs] * worklogs[Workdays]

)

3 Create Util Measure

Util =

DIVIDE ( SUM ( worklogs[BillHours] ), [WorkHours] )

Then, the result looks like this:

Fore more details, please refer the attached pbix file.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!

[ctedesco3307]

@v-cazheng-msft  this worked Great!!! thank you - I made one minor adjustment to count the Working Days in the calendar table rather then the weekdays and it worked perfect!! thank you so much! 

[v-cazheng-msft]

Hi @ctedesco3307 

Do you want to display BillHours and Utill values of current month according to different weeks?

May I know how you create relationships for these three tables?

Could you please tell me how you create your Util Measure?

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!

[ctedesco3307]

Hi @v-cazheng-msft 

Ultimately I'd like to display Bill Hours and util by the Date hierarchy, so starting from year, then drilling down to Quarters, Months, Weeks, Days. 

The table relationhips are

Employee.UserID = Worklogs.AUTHOR_ACCOUNT_ID
Calendar Table.date = Worklogs.WDate

The current calc I have for Util is 

Util = divide(Sum(Worklogs[BillHours]),40)  I'd like to replace the '40' - that represents the hours per week with a measure using the WrkHrs value from the employee table since not all emps work 40 hours a week and to make the measure dynamic. 

Please let me know if you need more intel.

Thank you 

[v-cazheng-msft]

Hi @ctedesco3307 

Thanks for your clearly description! I think I basically understand your needs. Please try this solution.

1 Create a Calculated column in worklogs table

RelatedWrkHrs = RELATED(Employee[WrkHrs])*5

2 Make some changes to your Util Measure

Util =

VAR MidT =

    SUMMARIZE(

        worklogs,

        worklogs[AUTHOR_NAME],

        "div", DIVIDE ( SUM ( worklogs[BillHours] ), SUM ( worklogs[RelatedWrkHrs] ) )

    )

RETURN

    SUMX (MidT, [div] )

Then, the result will look like this:

For more details, please refer to the attached pbix file.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!