Forum Discussion
Calculating the utilization based on dates
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 Mach...
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.
Plz share the pbix or the sample data. Will try to get exact solution
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.Hi,
please see below. is it correct?
Yes this is indeed the result I'm looking for, very curious how you managed to do so 🙂