Actual vs Planned - Time format vs Duration

[AaronRogers3]

Hi,

I have quite a complicated task. I am creating a dashboard and the guy who i am creating it for would like to see the planned time vs actual time.

The actual time is coming from a database where the format is in Time. e.g. 03:45:00.

The planned time is coming from a master schedule spreadsheet that i have been given. In the spreadsheet the value is: e.g.

20:39

However, when I add it as a source to my Power BI the value is:

0.860422217

I thought this was really weird and didnt understand it, although then I checked that the formula for it was:

=VLOOKUP(B1289,Items!A:F,6,FALSE)+(VLOOKUP(B1289,Items!A:L,8,FALSE)*D1289*0.041667)

This has made it really difficult for me to compare them or even get the planned value into PowerBI in the correct format.

When I change the data type in edit queries it doesnt seem to be quite right.

Can anyone offer me any suggestions? would be greatly appreciated!

[Anonymous]

Can't you just change the format?

The value you got (0.86) is 20,7 hours out of 24.

[AaronRogers3]

I tried going through the different data types.

The thing is i am not in charge of inputting the data so the way it is saved is not up to me.

What data type do you suggest so that both could work?

[Anonymous]

Could you send some prints of the data, tables and the different formats you have?

[AaronRogers3]

Okay so the Records table the Total Time column is my ACTUAL.

the MS excel table the DURATION column is the PLANNED.

So what i am trying to achieve is that my actual, say 05:00:00 (5 hours)

and the planned is 03:00:00 that i can see that this line took too long.

Although the decimal format is not allowing me to do this.

Thanks, appreciate this!

[Anonymous]

Create a seconds column:

= Table.AddColumn(Source, "Seconds", each [Time]*24*60*60)

Add a new time column:

= Table.AddColumn(#"Previous.Step", "New.Time", each #time(0,0,0)+#duration(0,0,0,[Seconds]))

[AaronRogers3]

I am trying to enter those into the DAX for new columns but am getting errors.

Apologies i would say i am quite the Power BI beginner.

Is there any way you could adapt them to my situation?

[Anonymous]

Go to the query editor instead and add new steps

[AaronRogers3]

Still receiving errors unfortunately 😞

[Anonymous]

@AaronRogers3

If it works please mark as solution

[Anonymous · Accepted solution]

Hi!

Go to Query Editor...

Add a new Custom Column...

Write this formula (change the name of the column for the one you have, if it is called Time then it's OK):

= [Time]*24*60*60

Click OK, a new column should be added.

Add a new Custom Column...

Write this Formula (again, adapt the column name if needed, here Seconds is the column you just created):

= #time(0,0,0)+#duration(0,0,0,[Seconds])

It should work, if it doesn't please describe the error you got.

Cheers!

[AaronRogers3]

Here is the error:

Expression.Error: We cannot apply operator * to types Time and Number.
Details:
    Operator=*
    Left=00:01:00
    Right=24

[Anonymous]

You should apply that to the column that has the "0.86XXXX" values and not the one that has the formatted time.

[AaronRogers3]

What would be the best way to compare them then?

When i put the planned vs actual into a visualization it just seems to COUNT them?

[Anonymous]

On the visuals field (right pane) on each field there is a small drop down arrow, click that and select "Don't Summarize".

[AaronRogers3]

Boom that worked!

Thanks!