Count available working hours for a specific period and compare with billed hours

[Anonymous]

Hi!

I have added a simple datetable with calendarauto (year, quarter, month, week, day, date) which I've connected with another table that contains order data (order rows). Everything works fine between the two tables.

In the order data table I have information about specific orders (order rows) like Resource/MachineID, Quantity billed hours, Price, ItemID etc. Now I would like to be able compare these billed hours with available working hours for every MachineID to get a billable % for each week, month, quarter, year or so.

I'm a newbie in Power BI and I put hours in googeling and youtubeing about networkdays and other functions but I have no clue where to start or how to solve this.

Do anyone know of a solution?

[tamerj1]

Hi @Anonymous 

please try

% hours =
DIVIDE (
SUM ( OdereDate[Billed Hours] ),
CALCULATE (
SUM ( OdereDate[Billed Hours] ),
ALLEXCEPT ( OdereDate, OdereDate[MachineID], OdereDate[Date] )
)
)

[Anonymous]

Thanks for you reply, @tamerj1 

I think I will first need a measure for available working hours in order to be able to compare billable hours with it. In 2022 there were like 2024 total work hours when excluding weekends and holidays with a workday of 8 hours. And the measure for available working hours must be possible to break down to every date in order to be able to compare it with billable hours for a specific date period.

[tamerj1]

@Anonymous 

You may use 

Working Days =
COUNTROWS ( FILTER ( datetable, datetable[Workday?] = "Yes" ) ) * 8