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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Hesherkid
New Member

Date Hierarchy not applying to all dates

Hello, 

 

I have created a custom rolling_calendar table and have a relationship to my other table ChildJobs which has dates coming in from JobFinishDates. I put together a table using my JobFinishDates date and rolling_calendar date hierarchy and it isn't applying to all the dates. When I change the relationship from childjobs to jobfinishdates it behaves the same. 

 

I created measures to make so that my dates are inline and they are, I've confirmed that the format for all date columns is the same. 

 

Hesherkid_0-1736879739593.png

Hesherkid_1-1736879925499.png

 

1 ACCEPTED SOLUTION
ToddChitt
Super User
Super User

You have values displayed that include a TIME element. Therefore I kind of assume that it is a Date/Time data type. 

 I don't care about formatting. I care about data TYPE. You can have a DATE/TIME data time and value of "1-Jan-22 12:34PM" but then FORMAT it as a DATE so it shows up and is displayed as "1-Jan-22". But under the covers that time element is still there. And any TIME other than 12:00 AM will NOT match to a pure DATE data type.

What are the DATA TYPES of the columns?

ToddChitt_0-1736888146516.png

 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
ToddChitt
Super User
Super User

You have values displayed that include a TIME element. Therefore I kind of assume that it is a Date/Time data type. 

 I don't care about formatting. I care about data TYPE. You can have a DATE/TIME data time and value of "1-Jan-22 12:34PM" but then FORMAT it as a DATE so it shows up and is displayed as "1-Jan-22". But under the covers that time element is still there. And any TIME other than 12:00 AM will NOT match to a pure DATE data type.

What are the DATA TYPES of the columns?

ToddChitt_0-1736888146516.png

 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Thank you ToddChitt for having me review those, the table that I am Splitting into the ParentJob and ChildJob had DATE/TIME where my ChildJobs, and JobFinishDates had DATE and that was the issue. Much appreciated 

ToddChitt
Super User
Super User

Can you show us some sample data from both the custom rolling dates table, and the table it is related to?

Can you verify that 4-Oct -22 is or is not in the dates table. 

Are both columns in the relationship actual DATE types (hint: i.e.: NOT date/time fields with a date format)?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Rolling_Calendar

Hesherkid_0-1736884184581.png

ChildJobs table

Hesherkid_1-1736884236758.png

JobFinishDates related to ChildJobs

Hesherkid_2-1736884280970.png

I created Measures to confirm the date ranges are covered. 

Hesherkid_3-1736884312413.png

All Date columns in all tables are formatted as 

Hesherkid_4-1736884356259.png

 

Thanks, 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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