Forum Discussion
selected_
Helper IV
Helper IVWhat's wrong the date or it's my data model?
I have a 3 tables with dates in each tables so I created a table just for DATE with this DAX code
- selected_
What does your date look like in the orignal tables? It needs to match the date table EXACTLY. I typically use a DateKey in the format YYYYMMDD so that there is no ambiguous dates that don't match, especially if you are using date/time this can be important.
4 Replies
- MattAllington
Community Champion
Best practice is to create a calendar table containing all dates from the start of the year of the first date in all fact tables to the end of the year of the last date in all fact tables. Personally I never create date tables in DAX. You can copy my power query table from here
https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/
and read about calendar tables here https://exceleratorbi.com.au/power-pivot-calendar-tables/
selected_- AllisonKennedyselected_
What does your date look like in the orignal tables? It needs to match the date table EXACTLY. I typically use a DateKey in the format YYYYMMDD so that there is no ambiguous dates that don't match, especially if you are using date/time this can be important.
DimDate_DAX = ADDCOLUMNS ( CALENDAR("2017-01-01",TODAY()), "Year", Year([Date]), "Quarter", CONCATENATE("Q",ROUNDUP(MONTH([Date])/3,0)), "Month", MONTH([Date]), "MonthName", FORMAT([Date],"MMMM"), "WeekNumber", WEEKNUM([Date]), "WeekDay",WEEKDAY([Date]), "Day", DAY([Date]), "DayName", FORMAT([Date],"DDDD") )