Modelling: Best Practices with Multiple Date Columns & Values

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