Reply
Frequent Visitor
Posts: 4
Registered: ‎12-27-2016

Date Hierarchy shows day of month not day of year

When I try to drill down usig a date hierarchy, I get a list of days by day number.  What I really want is to increase the granularity of the data.

 

Here is the use case:  I set up a line chart using Date as an axis.  Then I add a Count of something as a value.

 

When I first see the chart is shows all of the counts within the year, Click down on hierarchy anmd it shows all of the counts within the Quarter, Month, etc.

 

But when I click to Days, it shows me a list fo days (1..35) and it aggregates the data for all of the days with that day number (1, 2, ..31).

 

What I would expect to see is a drill down into granularity.  So if I have three months worth of data, I would see all 90 days worth of data not just 31.  1/1, 1/2...3/30, 3/31

 

What am I doing wrong?

Moderator
Posts: 3,051
Registered: ‎03-06-2016

Re: Date Hierarchy shows day of month not day of year


@jwieczerzak wrote:

When I try to drill down usig a date hierarchy, I get a list of days by day number.  What I really want is to increase the granularity of the data.

 

Here is the use case:  I set up a line chart using Date as an axis.  Then I add a Count of something as a value.

 

When I first see the chart is shows all of the counts within the year, Click down on hierarchy anmd it shows all of the counts within the Quarter, Month, etc.

 

But when I click to Days, it shows me a list fo days (1..35) and it aggregates the data for all of the days with that day number (1, 2, ..31).

 

What I would expect to see is a drill down into granularity.  So if I have three months worth of data, I would see all 90 days worth of data not just 31.  1/1, 1/2...3/30, 3/31

 

What am I doing wrong?


That is the hierarchy drilling down by design. What do you mean "all 90 days worth of data".

Highlighted
Visitor
Posts: 1
Registered: ‎05-12-2017

Re: Date Hierarchy shows day of month not day of year

Hi Eric,

 

Old thread, but it's asking the question I need answered.

 

I also have a bar chart plotting figures over days time, and I'm using a slicer to change the date range.

When I use the slicer to select one month of data only, the chart will show each day of the month (1st, 2nd, 3rd, 4th, etc.) just like it should - good.

I then change the slicer to show 3 months worth of data, BUT, the chart is still showing only "each day of the month" and not "each and every single day for that 3 month period".

i.e. It's still showing each of the 30/31 "days of the month", but now it's summed the 1st of each month and made that a single data bar, same with the 2nd, 3rd, 4th, etc., so that there are still only 30 data points on the chart even though I am slicing to 90 days.

What I want to see is all 90 days with their own data bar, not just 30 data bars with each day-of-the-month summed up into a single data bar.

Power BI, Days view issue.PNG

 

 

Moderator
Posts: 3,051
Registered: ‎03-06-2016

Re: Date Hierarchy shows day of month not day of year


@Stephen_42 wrote:

Hi Eric,

 

Old thread, but it's asking the question I need answered.

 

I also have a bar chart plotting figures over days time, and I'm using a slicer to change the date range.

When I use the slicer to select one month of data only, the chart will show each day of the month (1st, 2nd, 3rd, 4th, etc.) just like it should - good.

I then change the slicer to show 3 months worth of data, BUT, the chart is still showing only "each day of the month" and not "each and every single day for that 3 month period".

i.e. It's still showing each of the 30/31 "days of the month", but now it's summed the 1st of each month and made that a single data bar, same with the 2nd, 3rd, 4th, etc., so that there are still only 30 data points on the chart even though I am slicing to 90 days.

What I want to see is all 90 days with their own data bar, not just 30 data bars with each day-of-the-month summed up into a single data bar.

Power BI, Days view issue.PNG

 

 


 

 

 

 

 

 

 

 

 

 

@Stephen_42

What do you put on the X axis? It shall work if you put a date instead of date hierarchy.Capture.PNG

Frequent Visitor
Posts: 2
Registered: ‎11-13-2018

Re: Date Hierarchy shows day of month not day of year

Eric,

 

If you use the date instead of the date hierarchy, it does display all 90 days in Stephen's example.  But then you lose the ability to do any hierarchy groupings.  

 

Is there a way to change this?  I want to be able to drill down from Year to Quarter to Month (to Week?) to Day, without grouping in a confusing way. 

 

Example:  I have sales data from 2017 and 2018.  When I drill down from year to quarter, I want to see 2017 and 2018 turn into: 2017Q1, 2017Q2, 2017Q3, 2017Q4, 2018Q1, 2018Q2, 2018Q3, 2018Q4 instead of just 4 columns for Q1 to Q4.  It doesn't make much business sense to sum up 2017Q1 and 2018Q1 into one column called Q1, so I'm surprised this is the default behavior.  Further, I'm a little frustrated that I can't change this default...or can't figure out how to change it.

 

I've worked around this by creating custom columns called "YYYY0-QQ" or "YYYY-MM" but then I lose the nice drill down hierarchy.  Am I missing something?

Frequent Visitor
Posts: 7
Registered: ‎12-10-2018

Re: Date Hierarchy shows day of month not day of year

I am having this same issue and it seems like such a simple thing to be able to do. I think I may have solved for this. On my axis I have my date field with the hierarchy. I only have Month and Day as the pieces of the hierarchy there. On my chart, I use the expand next level and it shows all dates. When using the show next level, I had the same issue. I am not sure how this expand next level works though as it seemed buggy and wasn't working the first time (it just gave me a blank chart).