Forum Discussion
Date Hierarchy Not Available for Calculated Column
I have 2 columns in my data. CompletedVisitDate and ScheduledVisitDate. These both meet the status of Completed or Scheduled. To account for them together, I created a new column:
The new column populates correctly and fully. The column has a data type of Date and permits the format of mmmm-yyyy. Each row will have at least 1 date filled. The DateofService dates range from 3/29/2022 to 9/2/2022. And the ScheduledVisitDate ranges from 3/29/2022 to 11/1/2022.
But if I drop the new column onto a table, I do not get the Date Hierarchy options (Year, Quarter, Month, Day). I only get the date option. I want to aggregate these by Month and Year in a Line Chart.
I have a 1-to-Many relationship from Date in the Date Table to the MaxActionDate (calculated column) in my data.
Both DateofService and ScheduledVisitDate are formatted as date fields and behave like date fields. You can choose to display the date or the date hierarchy in a table visual.
So, why can I not use the calculated MaxActionDate like a hierarchical date column in a chart visual?
comish4lif2 , Ant date joined with date table marked as date table will not have hierarchy, You can create custom hierarchy and use
create a new hierarchy
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY
These are the reasons Date Hierarchy can be missing
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/td-p/525460
https://community.powerbi.com/t5/Desktop/Date-hierarchy-not-available/td-p/438804
https://community.powerbi.com/t5/Desktop/Lost-Missing-Date-Hierarchy/td-p/421045
4 Replies
comish4lif2 , Ant date joined with date table marked as date table will not have hierarchy, You can create custom hierarchy and use
create a new hierarchy
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY
These are the reasons Date Hierarchy can be missing
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/td-p/525460
https://community.powerbi.com/t5/Desktop/Date-hierarchy-not-available/td-p/438804
https://community.powerbi.com/t5/Desktop/Lost-Missing-Date-Hierarchy/td-p/421045Who marked this as Solved? It's my question, right? Don't I get to mark it as solved or not? Who is evelyn91011 ?
Hi,
Create calculated column formulas to extract Day, Month, Month name and Year in the Calendar Table. To your visuals/in your measures, refer to the fields from the Calendar Table.
I roomed with evelyn91011 in college, and she was always pulling stuff like this.
