Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RedZero
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
MFelix
Super User
Super User

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



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.

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



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.

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



Version: 2.73.5586.984 64-bit (September 2019)

 

Do you see an auto-generated date hierarchy?

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



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.