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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
comish4lif2
Advocate II
Advocate II

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: 

MaxActionDate = MAX (DISPOSITION[DateOfService] , DISPOSITION[ScheduledVisitDate] )

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?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

4 REPLIES 4
Whigpresident
Advocate I
Advocate I

I roomed with evelyn91011 in college, and she was always pulling stuff like this.

 

Ashish_Mathur
Super User
Super User

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.


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

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Who marked this as Solved? It's my question, right? Don't I get to mark it as solved or not? Who is @evelyn91011 ?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors