Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GilesWalker
Skilled Sharer
Skilled Sharer

Plotting times on a graph

Hi Everyone,

 

I have a dataset with a column containing the date and time. I have changed the format to show the data as HH:MM. I am trying to plot this on a line graph visual, however I cant seem to get it to do it correctly. The graph wont read the data as time and will only give the values as a COUNT. 

 

I need the graph to show the date on the X axis, the time on the Y axis and then the values will be the data in the column (arrival time).

 

I have tried a bunch of different things but can get it to work, hope you can help.

 

Thanks,

 

Giles

 

 

1 ACCEPTED SOLUTION
mike_honey
Memorable Member
Memorable Member

Starting from a single Date column, I would follow these steps:

  1. Using Power Bi Designer: Home / Edit Queries / select your Query
  2. Add a "Date" column - select your Date column, Add Column / Date / Date only
  3. Add an "Hour" column - select your Date column, Add Column / Time / Hour
  4. Add an "Minute" column - select your Date column, Add Column / Time / Minute
  5. calculate a "Hour Decimal" column in the Query Layer - Add Column / Add Custom Column / Hour Decimal = [Hour] + ( [Minute] / 60 ). So 3:15pm would become 15.25.
  6. Set the data type of the new "Hour Decimal" column  - Home / Data Type: Decimal Number
  7. Close & Apply
  8. Navigate to Data view, choose your table, choose the "Hour Decimal" column, navigate to Modeling and set Default Summarization: Minimum (depends on your requirements - this will show the first Time for each Day. You can customize this for each Visual).
  9. Navigate to the Report view, select your Line graph Visual, set the Axis to be the new "Date" field, and the Value to the new "Hour Decimal" field.

Now the Y-axis shows the Time portion of each date, from 0 - 24.

 

While figuring this out, I've built a working solution which you can download from my OneDrive and try out:

 

http://1drv.ms/1AzPAZp

 

It's the file: Power BI demo - Line chart of date vs time.pibx

 

The data is a random sample but hopefully it helps show the steps.

View solution in original post

5 REPLIES 5
SrujanaNeelam
New Member

I have a similar Issue. I have data with two colums and I want simple XY scatter plot. I want  date on axis and 96 times stamps(24 hour with 15 min interval on Y axis).Two months date on x -axis and 96 time stamps on Y axis.Just need XY scatter plot as in Excel.I tried but with no luck in p.ower BI.Everytime I am trying to drag date and time am I getting only the aggregation.Its very important requirement for us.Appreciate help.Thank You

mike_honey
Memorable Member
Memorable Member

Starting from a single Date column, I would follow these steps:

  1. Using Power Bi Designer: Home / Edit Queries / select your Query
  2. Add a "Date" column - select your Date column, Add Column / Date / Date only
  3. Add an "Hour" column - select your Date column, Add Column / Time / Hour
  4. Add an "Minute" column - select your Date column, Add Column / Time / Minute
  5. calculate a "Hour Decimal" column in the Query Layer - Add Column / Add Custom Column / Hour Decimal = [Hour] + ( [Minute] / 60 ). So 3:15pm would become 15.25.
  6. Set the data type of the new "Hour Decimal" column  - Home / Data Type: Decimal Number
  7. Close & Apply
  8. Navigate to Data view, choose your table, choose the "Hour Decimal" column, navigate to Modeling and set Default Summarization: Minimum (depends on your requirements - this will show the first Time for each Day. You can customize this for each Visual).
  9. Navigate to the Report view, select your Line graph Visual, set the Axis to be the new "Date" field, and the Value to the new "Hour Decimal" field.

Now the Y-axis shows the Time portion of each date, from 0 - 24.

 

While figuring this out, I've built a working solution which you can download from my OneDrive and try out:

 

http://1drv.ms/1AzPAZp

 

It's the file: Power BI demo - Line chart of date vs time.pibx

 

The data is a random sample but hopefully it helps show the steps.

Thank you.  This is really useful and I managed to create a Scatter Chart showing how carbo hydrate intake is spread over the day! 

 

Thanks a lot for this useful information!123.JPG

Thanks @mike_honey that worked perfectly. The write up was clear and the example you provided helped a lot.

 

Giles

WillT
Community Admin
Community Admin

Hi Giles. I'm not sure I follow you exactly. Your data is formatted something like this, right?

Date      Time

1/1/2016  12:34

1/2/2016  10:15

1/3/2016  19:25

 

And you want to show a line graph with dates along the X axis. Are you asking for the time to determine the height the line appears on the Y axis? So with my sample data, the value for 1/1 would be 12:34, 1/2 would be 10:15 etc? At the moment there's no automatic way to get Power BI to display time as Y-axis values, but you could convert the time values to a number and put that into the 'Values' box in the field well. You can convert it to the number of minutes past midnight by:

= HOUR([Time])*60 + MINUTE([Time])

 

Does that help? Maybe I've misunderstood your question...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors