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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Chthonian
Helper III
Helper III

Modelling: Best Practices with Multiple Date Columns & Values

Good morning all & happy Monday! 😎

This is more of a discussion piece rather than a request for help. I am trying to get to a best practices situation with my model development and wanted to get the community's thoughts around date handling.

 

Background:

After a bit of web crawling and youtube viewing, I came across a couple of tidbits of info relating to referential integrity, column and formatting best practices, and how null values can affect calculations (specifically averages) in PowerBI. Fast forward to looking at my own models and one of the situations I face is date columns where I am as likely to have null values as I am to have a 100% all date values in a respective column. 

 

My company (like most) have many many date fields for all sorts of time dimensions. One example I have is an 'Orders Completed' date column which will never have date values in rows where the order has not be completed. And so to the questions........

 

Questions:

  1. What are the best practices when it comes to handling null values in a date column?
  2. If using a default value (i.e. 01/01/1900) what is the best way to handle Date Table creation? A filtered CALENDARAUTO type of DAX created table or would it be better to go with M-Code query for the table?
  3. From a visual perspective the introduction of a date value outsuide of a date table's range would introduce (BLANK) values in visuals, so what is the best way forward?

Thanks Gents & Ladies, I am just keen to see how the big-guns handle such small matters.

😃

 

 

David

7 REPLIES 7
BA_Pete
Super User
Super User

Hi @Chthonian ,

 

My tuppence:

 

1) I think this depends on how you plan to use the dates. If you just want counts/averages where those dates exist or not, then you can handle nulls within your measures, for example:

_incompleteJobs =
CALCULATE(
    DISTINCTCOUNT(table[jobnumber]),
    ISBLANK(table[completedDate])
)

_completeJobs =
CALCULATE(
    DISTINCTCOUNT(table[jobnumber]),
    NOT ISBLANK(table[completedDate])
)

If you are visualising data over time, then you will need to either replace nulls with today's date, or create a helper column that does this while retaining the original data. In Power Query, I'd add a column like:

completedDateNoBlanks =
if [completedDate] = null then Date.From(DateTime.LocalNow()) else [completedDate]

 

2) With far-past defaults, such as 01/01/1900, you could again replace these values with something a bit closer to today, but before any true date you are likely to have in your data. This will avoid creating a ridiculously hefty calendar. Regarding calendar table set up, I will always, always, go with M code/Power Query. Why would you want this using system memory at runtime when you can offload the work to the PBI Service refresh?

 

3) If there is a high chance that you're going to get odd dates popping up in your data, then you can dynamically create your calendar to pick this up at creation time. Here's a few examples of calendar source lines in M that achieve this:

Earliest data date to today
    Source = { Number.From(List.Min(table[Date]))..Number.From(DateTime.Date(DateTime.LocalNow())) },
	
Earliest data date to latest data date
    Source = { Number.From(List.Min(table[Date]))..Number.From(List.Max(anotherTable[Date])) },

1st April of earliest data year to today
    Source = { Number.From(#date(Date.Year(List.Min(table[Date])),4,1))..Number.From(DateTime.Date(DateTime.LocalNow()))},

 As before, if these outlier dates are known defaults, then I'd replace them out with something more friendly that doesn't interfere with my 'real' data dates.

 

Best,

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks for the great reply @BA_Pete I appreciate the effort.

 

It would seem that I am on the right path already then, with regards to points 1 & 2 this is something I am already doing and as I meantioned earlier I just wanted to make sure that I was implementing bad practices. 

 

As for point 3, I will be honest and admit that I had not even considered outlying dates in my m-code so thanks a million for that pointer. 

 

Loving your work! 

David

amitchandak
Super User
Super User

@Chthonian , What value is shown in the column when the value was blank is fine. We can check like

 

isblank([Date]) .

 

Auto calendar is a good option. But typically organizations have calendar +/- 2 years on both sides of data.

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/


Appreciate your Kudos.

Thanks @amitchandak ,

 

I always setup my models with a proper date table and relationships, something I learned right at the start 😃

 

I do currently utilise ISBLANK('Date'[Date]) to work with my blanks. Are you saying the blank/null values are not something to be concerned about? I always want to model with best practices so just wanted to make sure I was not making some rookie mistakes. 

 

Thanks again for the reply.

David

tex628
Community Champion
Community Champion

Blanks and nulls provide just as much information about a record as any full date. In some of my installations running on Dynamics Navision data I've seen blank values being converted to 1753-01-01, if you ever come across this my advice is to convert to proper null values. 

Br,
J


Connect on LinkedIn

Thanks 

 

@tex628 , I couldn't agree more about the informational value of the blanks/nulls. The majority is coming from Dynamics CRM but I have not seen that date but will keep an eye out going forward.

 

Cheers,

David

tex628
Community Champion
Community Champion

Another advice is to set up the calendar table in Power BI Dataflows. You can take the same power query code that you usually use and create it there. This simply means that you can use the same calendar for every report in the organization and you furthermore add flags for workdays, YTD/MTD etc.. etc.. 

/ J


Connect on LinkedIn

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.