Calculating cost of duration/time spent on a task

[ProverbialPaul]

Hello all

I have a table that records the start and finish time of repairing an item and a calculated column that works out the associated duration.

From this, I have two measures to take the decimal duration figure to turn it into hours/mins and, separately, minutes spent on the task so I can show that in my reports.

I now want to work out how I can calculate, using a measure, the cost of this time. I'm not sure if I should be using the calculated duration figure or either one of the measures to accomplish this.

I don't have the labour cost recorded in the same table as the calculated duration column but I can add it into another table to use a lookup function if need be. I tried a multitude of ways on my own but I just can't seem to work this one out.

Hoping someone can help. Thank you

[ProverbialPaul]

Hello @danextian

I have a start and finish time:

I have a duration column:

Inside my report, I have 2 measures calculating hours and minutes taken using the duration:

And, separately, but similar to the above DAX function, just the minutes taken.

I would now like to create a measue to calculate the cost of the time taken to complete each task but I'm unsure how I should be going about this.

The outcome is to have a card in the report with a dollar figure showing the labour cost of the time taken to complete the selected tasks.

The main table is built as per the following:

so it doesn't make sense to record the hourly labour rate ($30) or rate per minute ($0.5) against each item in this table so I was hoping to achieve the outcome using a measure or calculated column.

I hope this makes more sense. I appreciate your help. Thank you

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.  Share the othe table of hourly rates as well. 

[ProverbialPaul]

Hi @Ashish_Mathur 

I don't currently have PBI pro so I can't share links unfortunately. Unless there's another way I can do it?

Also, the report doesn't currently have a table containing the hourly rate as I deleted it because I thought I was doing it the wrong way.

Thank you

[Ashish_Mathur]

I will need some file to work with.  You will defeinitely need the hourly rate table.

[ProverbialPaul]

Hi guys

Sorry, I never thought about using an external storage facility.

Link to PBI file is here:

https://drive.google.com/file/d/1ex7yJ7Z6FUDc40NQ0Bq1csO22CYpFtVK/view?usp=sharing

I re-created the labour cost table:

and tried a similar function to one I had used previously to calculate cost which looks like this:

Labour cost =

SUM ('Module Repairs'[Time taken - Duration]) * LOOKUPVALUE ('Labour cost'[Rate],'Labour cost'[Unit of time], "Hour")

But if you look at the "Daily totals" tab I get a total of $5.9375 when it should be $142.50. I'm stumped!

I think it has something to do with me using the duration calculation but I'm really not sure anymore.

Thank you

[Ashish_Mathur · Accepted solution]

The measure can be as simple as this

Measure = SUM('Module Repairs'[Time taken - Duration])*30*24

There is no need for the Labour cost table.  Sorry for that incorrect suggestion.

[ProverbialPaul]

Thank you so much @Ashish_Mathur 

I really appreciate your help.

I had a feeling it was going to be simple but I was going cross eyed.

[Ashish_Mathur]

You are welcome.

[danextian]

You can use Google Drive, One Drive or Dropbox.

[danextian]

Hi @ProverbialPaul ,

Please post a sample data and your expected result.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...