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
kennyyip
Helper I
Helper I

Maintain date hierachy with date table

Hi,

I have a data set "revenue" with revenue and date (only month end date involved but it's still down to day level). To use datesadd and tablesytd function I created a date table "Calendar" with one column using calendarauto. Then I relate the revenue[date] to calendar[date], one to many and two way cross filter relationship. However, when I use revenue[date] in x axis of column chart, it lost the default hierachy that revenue[date] (e.g. Year, quarter, month, date] had before linking to calendar table. Any method I could have that when I relate revenue[date] to calendar[date]? Thanks.

Kenny
1 ACCEPTED SOLUTION
5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

The relatioship from revenue[date] to calendar[date] should be a many to one relationship.  In the visual, drag Date from the Calendar Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

To conclude, my current problems when using respective columns in the x axis of column charts:

1. Revenue[date]: no date hierachy in the chart (i.e top left drill down button in chart widget)

2. Calendar[date]: ytd prior year measures show blank value

3. Year-month-day hierachy from calendar[date]: ytd prior year measures show blank value, and column in x axis arranged as 2018 1, 2018 2, 2018 3, 2017 1, 2017 2....2017 12

Any advice? Thanks a lot

Kenny

Sorry, my bad to describe not accurately. You are right it's one to many from Calendar to Revenue. But I still couldn't get the date hierachy after relating both

Hi,

 

I always follow the best practise of creating Year and Month columns in the Calendar table and then dragging those to the visuals.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.