Forum Discussion
Modelling: Best Practices with Multiple Date Columns & Values
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
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,
JThanks
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
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
