Forum Discussion
Plotting times on a graph
- 10 years ago
Starting from a single Date column, I would follow these steps:
- Using Power Bi Designer: Home / Edit Queries / select your Query
- Add a "Date" column - select your Date column, Add Column / Date / Date only
- Add an "Hour" column - select your Date column, Add Column / Time / Hour
- Add an "Minute" column - select your Date column, Add Column / Time / Minute
- 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.
- Set the data type of the new "Hour Decimal" column - Home / Data Type: Decimal Number
- Close & Apply
- 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).
- 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:
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.
Starting from a single Date column, I would follow these steps:
- Using Power Bi Designer: Home / Edit Queries / select your Query
- Add a "Date" column - select your Date column, Add Column / Date / Date only
- Add an "Hour" column - select your Date column, Add Column / Time / Hour
- Add an "Minute" column - select your Date column, Add Column / Time / Minute
- 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.
- Set the data type of the new "Hour Decimal" column - Home / Data Type: Decimal Number
- Close & Apply
- 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).
- 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:
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.
- GilesWalker10 years agoSkilled Sharer
Thanks mike_honey that worked perfectly. The write up was clear and the example you provided helped a lot.
Giles
- tmandou9 years agoNew Member
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!