cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Drill-Through Date Hierarchies

Hi folks,

 

two questions:

1) Date Hierarchie Auto-Generation

I have a two tables linked with a relationship

  • a date_lookup table with Dates (formatted as DateTime) and
  • a revenue data table (with values and a corresponding date)

When creating a chart (x: date, y: revenue value) no drill through is available. I expected an auto-generated date hierarchy that I can navigate through. The "Auto Date/Time" option is checked. Any idea why that is?

 

2) Self-Made Date Hierarchies

I created additional columns in my date_lookup table with calendar weeks (number), months, years. When creating my own hierarchy using those, I can drill through my time, but data is for the same time intervalls in different periods (e.g. January of 2017 and 2018) gets accumulated, same for calendar weeks. I guess, this is because the added column strips away everything but the week number/month/whatever. How can I preserve differentiation?

 

Thank you so much for your help!

 

Power BI Version: 2.73.5586.802 64-bit (September 2019)

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: Drill-Through Date Hierarchies

Hi @RedZero ,

 

I think that you should read the two articles below that explain a little bit better the date time in PBI:

https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/

https://radacad.com/power-bi-date-dimension-default-or-custom-is-it-confusing

 

Regarding your questions:

 

1) Date Hierarchie Auto-Generation

I'm assuming that you have a relationship between both tables with a one-to-many being the one side on the date_lookup so this must be the one used on the X-axis other wise the hierarchy will not be uploaded on the axis.

Other rhing check if on the axis value if you click on the arrow on the rigth side appears only the date option or the hierarchy option.

 

2) Self-Made Date Hierarchies

Be aware that when creating the separet columns this should not be lost, how are you placing the hierarchy? Week, Month and Year or Year, Month and Week?

If you have the first option you will get the aggregation as you are refering since the context is starting at week so it get's all the weeks that are number 1, 2 and so on no matter what is the year.

So order that you make your hierarchy is the one that probably is not preserving your diffferentiation.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Regular Visitor

Re: Drill-Through Date Hierarchies

Thank you, @MFelix , for your answer and links to further reading!

 

I do have a one-to-many relationship (the date_lookup being at the 'one'-side). When plotting over time I always use the Date field from the lookup table (I also chose option to 'hide the other dates in report view').

 

> Other rhing check if on the axis value if you click on the arrow on the rigth side appears only the date option or the hierarchy option.

Well, there is only a Date field. No Date Hierarchy is auto-generated. (see screenshot)datehierarchy.png

 

When using a custom date hierarchy to circumvent this problem, I order the time frames top down like this:datehierarchy2.png

This also shows how data from more then one year gets accumulated into the value for a single month (I did not select a specific year in the date hierarchy one level above). Is that what you meant with order? Btw, the columns in my data model are generated via 'add column>'

For the sake of completion, this is the query for the date_lookup:date_lookup.png

 

I hope that clarifies things.

Highlighted
Super User III
Super User III

Re: Drill-Through Date Hierarchies

Hi  @RedZero ,

 

From what I can see from the images you share this should work fine, the only thing I see is that your X-axis is continuous an not categorical what can make some problems on the way the information is presented.

 

Can you share a sample of your file please?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Regular Visitor

Re: Drill-Through Date Hierarchies

Sure, and thank you so much for taking the time @MFelix , I really appreciate it!

 

I boiled it down to a MWE. The .pbix and the referenced data can be found here. I hope that works.

Highlighted
Super User III
Super User III

Re: Drill-Through Date Hierarchies

Hi @RedZero ,

 

Based on the test I'm doing everything is working correctly when I drill down it only gives the values for one year, if I drill down all the way it give the values per yer, month week.

 

What is the version you are using on PBI?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Regular Visitor

Re: Drill-Through Date Hierarchies

Version: 2.73.5586.984 64-bit (September 2019)

 

Do you see an auto-generated date hierarchy?

Highlighted
Super User III
Super User III

Re: Drill-Through Date Hierarchies

Hi @RedZero ,

 

I don't have any hierarchy because I have the Auto-Time date option turn-off on my computer, but using the manual one that you created is working correctly.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Regular Visitor

Re: Drill-Through Date Hierarchies

Thank you @MFelix for taking the time and testing! I think I used drill-through incorrectly. And maybe this Auto-Generation is just a bug.

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors