Forum Discussion
Solved
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
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
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/
5 Replies
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.
- 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.
- 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
KennyI 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/
