Line Graph Showing Progression Of Time Over A Date Range

[DamienJ]

Hi,

I have a data set similar to the below:

Date DD/MM/YYYY / Time HH:MM:SS

01/01/2017	00:45:00
12/03/2017	00:44:31
21/05/2017	00:43:51
15/07/2017	00:46:01
08/08/2017	00:44:15

I'm trying to create a line graph to show the progression of time over the year that would look similar to the following highly technical image: (X axis scrawl is J F M A for months, Y is time in mins)

But for the life of me I can't figure out how to mirror this in Power BI.  It will either count the number of times that the time appears in the dataset, or will count the total number of rows in the dataset.  Can anyone assist please?

Thanks

Damien 

[v-yulgu-msft · Accepted solution]

Hi @DamienJ,

In a chart visual, only numeric values are allowed to be added to Y-axis. If we drag non-numerical fields into Y-axis, it will be counted automatically.

To achieve your requirement, you should first convert Time format filed to numerical type.

Time in minute = MINUTE('Date/Time'[Time])

Then, create a line chart like below.

Best regards,
Yuliana Gu

[DamienJ]

Hi @v-yulgu-msft and thanks for your reply.  That approach had never crossed my mind - thanks very much!  I did make a slight change to your suggestion as I needed the precision of seconds, and as the data is currently in Excel I used the following to convert the HH MM SS value to seconds: 

=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)

(Other sites suggested multiplying the value by 86400 but this gave erroneous results in some cases)

Your screenshot suggests Power BI will be very happy with these new numbers.  Thanks again!