Plotting data with a date dd/mmm/yyyy hh:mm:ss AM PM every every 2 hours

[Anonymous]

Using DirectQuery, I'm trying to plot volume sold with a trend line of price every 2 hours. Unfortunately all the values for a particular day either get averaged or summed (no option for no summary).

Power BI Community, is there a way to plot the different hours or even minuites on the same day? Something in the ball park of whats below

*insert price trend line*

                                                                                                                          x

           x                                                                                                             x

           x                                       x                                                                    x

           x                                       x                                                                    x

June 1st 10:00 AM           June 1st  11:00 AM         ......................            June 2nd 10:00 AM

[v-yulgu-msft]

Hi @Anonymous,

In your scenario, is the date column formatted as "dd/mmm/yyyy hh:mm:ss"? If so, when you directly add this column into axis section, don't select the "Date Hierarchy", by default, the axis will automatically divide datetime every 6 hours. The values won't be summed for a particular day.

As there existing some limitations for Direct Query mode, if the column in your table are date type without time part, I don't think we can divide it into datetime every 2 hours. The workaround is that you should modify the source table structure to mark it contain time values. In other words, the data records should be grouped every 2 hours for each day.

Best regards,
Yuliana Gu

[Anonymous · Accepted solution]

Thanks @v-yulgu-msft,

I found that creating a custom Hierarchy out of a selection of custom bins did the trick 🙂