Continuous X-Axis with Custom Date Hierarchy

[mgros1234]

Hi,

I am working with a dataset that has timestamps every 15 minutes. I need to create a simple line chart that can drill down on the actual timestamp and then roll up to daily, monthly, and yearly levels. I was able to create a custom date hierarchy that had all these levels, but it is fixed on a categorical axis. This creates a scroll bar and does not auto-size the axis, which is not functional with several months of data. 

When using the default date hierarchy (year, quarter, month, day), it seems you are able to create a continuous axis, but this fails to support my need to drill deeper than a day. Additionally, I found that you can remove the hierarchy alltogether and select the timestamp field only, but this does not allow me to roll up to daily, monthly, yearly levels.

Is there a workaround for creating a custom hierarchy with a continous or auto-sizing X-axis? I have searched all over and can't seem to find a solution.

Link to another post that has a similar question with no helpful answer: https://community.powerbi.com/t5/Desktop/Continuous-hierarchy-axis/m-p/395530#M180446

[v-kelly-msft]

Hi @mgros1234 ，

I suggest you use drillthrough funtion:

Remove the hierarchy alltogether and select the timestamp field only,then create 4 columns to calculate out the related day,month,quarter and year,such as below:

Finally create a drillthrough page,there create 4 line charts,showing the data in days,months,quarters and years.

Here is the reference about drillthrough.

If you need,pls click here to get my .pbix file.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mgros1234]

@v-kelly-msft sorry for the delayed response. This drillthrough workaround is not going to work for me because I need to have several visuals on a single page and having to drillthrough on each of them is going to create a large amount of extra visuals. If there was a way to do the reverse of your suggestion and have a date hierarchy with a drillthrough into the actual timestamp, that may work as there is only one extra visual, but I do not believe this is possible. I am really hoping Power BI will add a continuous X-Axis when using a custom date hierarchy. 

[v-kelly-msft · Accepted solution]

Hi @mgros1234 ，

I have a new idea: create four columns:Year ,Quarter, Month,Day,using the following dax expressions:

Year = YEAR('Table'[TimeStamp])

Quarter = QUARTER('Table'[TimeStamp])

Month = MONTH('Table'[TimeStamp])

Day = DAY('Table'[TimeStamp])

Then create a line chart without using date hierarchy,and put all the columns in X-axis:

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Can you provide a sample/example representation of your data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[mgros1234]

Sure thing, I'm not able to attach the file but I have replicated the table structure below. I am trying to chart the "Sample Data" column over time.

TimeStamp	Sample Data
3/2/20 11:00 PM	273
3/2/20 11:15 PM	275
3/2/20 11:30 PM	236
3/2/20 11:45 PM	298
3/3/20 12:00 AM	285
3/3/20 12:15 AM	216
3/3/20 12:30 AM	211
3/3/20 12:45 AM	200
3/3/20 1:00 AM	289
3/3/20 1:15 AM	286
3/3/20 1:30 AM	287
3/3/20 1:45 AM	221
3/3/20 2:00 AM	265