Modelling: Best Practices with Multiple Date Columns & Values

[Chthonian]

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

[BA_Pete]

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

[Chthonian]

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]

@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.

[Chthonian]

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]

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

[Chthonian]

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]

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