search

sql-bi / DaxDateTemplate

Power BI template to create a Date table in DAX
MIT License
265 stars 76 forks source link

Data Type question #47

Closed skorvek closed 2 years ago

skorvek commented 3 years ago

Any reason the data type for date fields is left as date/time versus date?

marcosqlbi commented 3 years ago

Technically, the data model has only one data type - DateTime. I agree that it could be modified to Date in Power BI just for visualization purposes. I keep this item open so I'll consider it in the next release.

DavidS-BDO commented 3 years ago

Another question regarding types and best practices.

Is there a reason we should convert calendar columns to Strings, instead of leaving them as a date and setting the format string. This would increase end-user flexibility by allowing alteration to the format string without access to the model and allow model builders to copy and paste DAX into any report without the need to set sort columns.

Example: Fiscal Month Year Current: ([...] "Fiscal Month Year", FORMAT ( FiscalDate, "mmm" ) & " " & CalYear [...]) Alteration: ([...] EOMONTH ( FiscalDate, 0 ) [...])

marcosqlbi commented 3 years ago

We explained this approach in this article: https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/

At the moment, not all of the visuals in Power BI are "friendly" with this approach. If it works for you, go ahead and use that technique. For the future, I'm thinking of a different way to generate the desired date table, but we are far from something to deliver. Thanks for the feedback!

marcosqlbi commented 2 years ago

Data types of date columns set to "Date" in v1.15