cancel
Showing results for 
Search instead for 
Did you mean: 
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.

 






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.

 






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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!