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 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'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.

7 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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?

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    2 years ago

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

    • 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.