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.

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?