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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hayesla96
Frequent Visitor

Best Practices for Data Model with Multiple Dates

Hello!

 

I am looking for some advice on a few things. Just some background, I am in logistics so my data has a lot of date/time columns. Some questions I am looking to answer is Ontime pickup and delivery percentages for drivers, detention (driver is on location over 2 hours) percentages, how many late shipments do we have today (unplanned within 3 hour of pickup cutoff, not dispatched before 12pm of the pickup date, driver is tracking late to location based on mileage to location 35mi/hour). And finally the company uses a 4-4-5 fiscal calendar, so all reports a month and over should relate to that calendar


What are the best practices for dealing with multiple date/time columns?

How do I create a dynamic 4-4-5 calendar?

And if I do have to build a date calendar, do I have to build one for each date/time column since PBI only allows 1 active relationship?

 

I'm not sure these are even the right question to be asking based on my data model... I am just not at the experience level to plan this out on my own. I would appreciate anyone who takes the time to help!

 

thank you!

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@hayesla96  All questions are valid, glad you are asking before you get too deep in your report/model development.

 

There are many articles on how to create calendar 4-4-5, and I'm not going to talk about that. You can just google it.

 

2nd, it is always preferred to have one single calendar table in the model and create inactive relationship with your date columns which is fine, and then create explicit measure and use USERELATIONSHIP DAX function in the measure to make inactive relation active in the context of that measure. I hope it helps.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

1 REPLY 1
parry2k
Super User
Super User

@hayesla96  All questions are valid, glad you are asking before you get too deep in your report/model development.

 

There are many articles on how to create calendar 4-4-5, and I'm not going to talk about that. You can just google it.

 

2nd, it is always preferred to have one single calendar table in the model and create inactive relationship with your date columns which is fine, and then create explicit measure and use USERELATIONSHIP DAX function in the measure to make inactive relation active in the context of that measure. I hope it helps.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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