Maintain date hierachy with date table

[kennyyip]

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

[Ashish_Mathur]

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.

[kennyyip]

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

[kennyyip · Accepted solution]

I solved my problem with reference to following links

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

https://sqldusty.com/2015/09/01/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-1/

[kennyyip]

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

[Ashish_Mathur]

Hi,

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