Calculate service hours spent in a day

[Mohanad-Mustafa]

Hi Team,

I have a table below that shows labour hours spent for one technician at clients sites in a month.

I need to perform the following dax formula to calculate labour hours spent in a day.

Formula is= Total hours spent /18.33(Number of working days in a month) x Number of months that have data in the table

So to put the example in action, here is what the number should be based on the numbers below in the table:-

187/18.33x4= 40.80 rounded

Ps: I have a full calendar table in my PBIX file

Thanks in advance 

[lbendlin]

sounds good. What have you tried and where are you stuck?

[Mohanad-Mustafa]

I am honestly not sure how to do it, I tried monthnumber function but its not correct, do you have any suggestion please?

[v-jingzhan-msft]

Hi @Mohanad-Mustafa 

You can try this measure. It assumes that [FY24] is a measure which calculates the hours. 

hrs =
VAR _table =
    FILTER (
        SUMMARIZE ( 'calendar', 'calendar'[FiscalMonth], "totalHrs", [FY24] ),
        [totalHrs] > 0
    )
RETURN
    SUMX ( _table, [totalHrs] ) / 18.33 * COUNTROWS ( _table )

If it doesn't work, please provide some sample data as @lbendlin has mentioned. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...