topic Re: Calculating the utilization based on dates in DAX Commands and Tips

Calculating the utilization based on dates

Anonymous — Thu, 16 Nov 2023 11:22:51 GMT

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!

Re: Calculating the utilization based on dates

Rupak_bi — Fri, 17 Nov 2023 12:10:07 GMT

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.

Re: Calculating the utilization based on dates

Anonymous — Sat, 18 Nov 2023 10:24:27 GMT

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?

Re: Calculating the utilization based on dates

Rupak_bi — Sat, 18 Nov 2023 17:39:26 GMT

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

Re: Calculating the utilization based on dates

Anonymous — Mon, 20 Nov 2023 10:16:19 GMT

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.

Re: Calculating the utilization based on dates

Rupak_bi — Tue, 21 Nov 2023 12:44:35 GMT

Hi,

please see below. is it correct?

Re: Calculating the utilization based on dates

Anonymous — Tue, 21 Nov 2023 12:48:05 GMT

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

Re: Calculating the utilization based on dates

Rupak_bi — Tue, 21 Nov 2023 12:53:38 GMT

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.