Average Time in chart

[rolfstar]

Hello! 

I have raw data like this

  https://dropmefiles.com/p7Mqd

so first i'm trying to get min and max time by measeres per day 

1) Measure MinTime = MINX(SUMMARIZE(raw;[Date];[Time];"A"; MIN(raw[Time]));[A])

2) Measure MaxTime = MAXX(SUMMARIZE(raw;[Date];[Time];"A"; MAX(raw[Time]));[A])

Then i get duration

Duration = 'raw'[Measure MaxTime]-'raw'[Measure MinTime] 

And next i got a little stucked because it shows average duration in 10 november - 10 hours and this is not correct.

Even if we think that's correct after that i cant make chart with X - date, Y - hours.

[Ashish_Mathur]

Hi,

Why is 10 hours incorrect?  19:00 less 9:00 is 10 hours.

[rolfstar]

Hello!

Duration of John on 10 nov is 10 (19-9)

Duration of Mary on 10 nov is 8 (19-11)

Duration of Bill in fact is 0, so we miss it

So average duration is 9 ((10+8)/2)

[rolfstar]

OK, now i went trough first part simply with group by https://dropmefiles.com/B9rGs

now i have correct values, but still can't understand how to make a chart with X and Y 

[HotChilli · Accepted solution]

Hi,  I've just had a look at your pbix.

The Duration column won't chart properly because it doesn't have the correct datatype.  It is set as Time which is not correct, it needs to be a decimal or whole number.  That means the calculation of Duration needs to change.  Use DATEDIFF and decide which units you want (HOURS, MINUTES or SECONDS).

You will then be able to chart Date against Average Duration.   You might have to write a measure to alter the average calculation because powerbi will not throw away the line with 0 duration.