Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
  • Rupak_bi's avatar
    Rupak_bi
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

MachineDepartmentHours available per monthRented from dateRented to date
1A601-1-202331-1-2023
2A501-11-202228-2-2023
3A601-10-202230-4-2023
4B501-2-202313-10-2023
5B701-10-202231-3-2023

 

Hours logged:

DateMachineNumber of hours
1-1-2023310
2-1-202312
2-1-202332
3-1-202325
4-1-202331
5-1-202356


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's avatar
Rupak_bi
Icon for Super User rankSuper User
2 years ago

Hi,

 

please see below. is it correct?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • Rupak_bi's avatar
      Rupak_bi
      Icon for Super User rankSuper User
      2 years ago

      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.