Calculating the utilization based on dates

[Anonymous]

Hi, 

For a group of machines which are a certain number of hours available per month I want to calculate the utilization rate. My (simplified) data looks like this, with the first picture the Machines and the second the Hours logged table. 

I have used a measure to calculate the utilization: 

Utilization = SUM('Hours logged'[Number of hours])/ SUM(Machines[Hours available per month])
So far this is all working, but I also wan't the utilization per department. A simple matrix visual can be seen below:

For department B, the utilization is not as I wan't it to be. Machine #4 is not rented until 1-2-2023, so I do not want these available hours included in the Sum of hours available in January. 
I tried several measures such as:

Adjusted hours available in period = IF(AVERAGE(Machines[Rented from date])> min(TBLDate[Date]), [Hours available per month], 0)

However, I also cannot get this to work, since this uses a single value for the date, but it should be evaluated for every single machine. 
I was thinking of making an extra table with all the months/machines/amount of hours available in that month, but I'm hoping there is an easier way. 

Any help would be much appreciated!

[Rupak_bi]

Plz share the pbix or the sample data. Will try to get exact solution

[Anonymous]

Hello Rupak_bi, I can't upload any file, but my data tables are:
Machines:

Machine	Department	Hours available per month	Rented from date	Rented to date
1	A	60	1-1-2023	31-1-2023
2	A	50	1-11-2022	28-2-2023
3	A	60	1-10-2022	30-4-2023
4	B	50	1-2-2023	13-10-2023
5	B	70	1-10-2022	31-3-2023

Hours logged:

Date	Machine	Number of hours
1-1-2023	3	10
2-1-2023	1	2
2-1-2023	3	2
3-1-2023	2	5
4-1-2023	3	1
5-1-2023	5	6

TblDate:

TblDate = CALENDAR(Date(2023,01,01), DATE(2023,06,30))

Month = MONTH([Date])

Week = WEEKNUM([Date])

And the measure I used for the utilization is:

Utilization = SUM('Hours logged'[Number of hours])/ SUM(Machines[Hours available per month])

I hope this works as well for you.

[Rupak_bi]

Hi,

please see below. is it correct?

[Anonymous]

Yes this is indeed the result I'm looking for, very curious how you managed to do so 🙂

[Rupak_bi · Accepted solution]

Step 1: Make a date table: 

Table = CALENDARAUTO()

Step 2: make a measure:

hours available =

sumx(filter(Machines,Machines[Rented from date]<=max('Table'[Date])),Machines[Hours available per month])
step 3: Make another measure:

hours logged = calculate(sum('Hours logged'[Number of hours]))

Step 4: Make another Measure:

ut = 'Hours logged'[hours logged]/[hours available]

 Step 4: Now use all these to make the matrix
Step 5: Make a slicer using new date table and set it to 1st January.

[Rupak_bi]

create a measure to calculate available hrs as below

calculate(sum(Machines[Hours available per month]),month(selectedvalue(date))>=rented from date &&month(selectedvalue(date))<rented to date.

now use this measure to calculate utilization.

[Anonymous]

Thank you for your reply, but when I try this measure no values are displayed. To be clear, when referring to date, do you mean the date of the dates table or the date in the Hours logged table?