Forum Discussion

GilesWalker's avatar
GilesWalker
Skilled Sharer
10 years ago
Solved

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...
  • mike_honey's avatar
    10 years ago

    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.