What to watch out for when implementing a calendar table?

[Anonymous]

Hi guys, 
I'm relatively new to PowerBI but come from a background in SQL and Data Warehousing.
Looking for some guidance as to what I should look out for when implementing a calendar table into a data model. 
The data set I'm working with is corporate so I can't reveal too much. But think of it as similar to an IT Service Desk. With times for tasks being received, logged, attended and completed. 

I've been trying to implement a calendar table into the data model for time intelligence and slicers. However when using the calendar table, my visualisations and tables go blank. I have checked the relationship types, filter direction, data types. My current workaround is to extract the required parts of a date column within the data set which works nicely. However, I am needing to use the DateDiff function to work out how long it takes for email tasks to be logged, filtering  out weekends and non working hours. So a calendar table would be useful. 

Here is the DAX used.

Dates =

VAR DAYS = CALENDAR( MIN(FM_Task_Detail[Task Logged Date]), MAX(FM_Task_Detail[Task Logged Date]))

RETURN

ADDCOLUMNS(

    DAYS,

    "Year", YEAR( [Date] ),

    "Month Number", MONTH( [Date] ),

    "Month", FORMAT([Date], "MMMM"),

    "Year Month Number", YEAR ([Date]) * 12 + MONTH( [Date] ) -1,

    "Year Month", FORMAT ( [Date], "MM YY" ),

    "DateKey", FORMAT ( [Date], "YYMMDD" )

)

Thanks a million!

[jdbuchanan71]

I would say on of the big gotchas in time intelligent functions is that your calendar table (the one you mark as a date table) must contain FULL years or the time intelligent calcs can return odd results.  If your table runs from 7/3/2015 - 10/5/2020, the time intelligence calcs will return results, they might be incorrect.

I would also change the way you calc the year month so you get 201906 for June 2019, makes it a bit easier to read.
I modified your code a bit to force the correct start and end.

Dates = 
VAR LowDate = MIN ( FM_Task_Detail[Task Logged Date] )
VAR HighDate = MAX ( FM_Task_Detail[Task Logged Date] )
VAR Days = CALENDAR ( DATE ( YEAR ( LowDate ),1 ,1 ), DATE ( YEAR ( HighDate ),12 ,31 ) )

RETURN 
ADDCOLUMNS(
    DAYS,
    "Year", YEAR( [Date] ),
    "Month Number", MONTH( [Date] ),
    "Month", FORMAT([Date], "MMMM"),
    "Year Month Number", YEAR ([Date]) * 100 + MONTH( [Date] ) ,
    "Year Month", FORMAT ( [Date], "MM YY" ),
    "DateKey", FORMAT ( [Date], "YYMMDD" )
)

Also, don't forget to mark it as a date table in PowerBI.
The gentlemen over at SQLBI have put together a great DAX date table template that shows a lot of what is possible.  https://www.sqlbi.com/tools/dax-date-template/

[Anonymous]

Another wee question. 
Is there anything I can do with date columns to make them more suitable for connecting with the date table? 
The columns don't contain blanks, but there is a variety of dates, not full years as you've mentioned. 

Thanks again 

[Anonymous]

Hi JD, 
Thank you very much for responding. 
Now that I think of it, the field I am using for the relationship between the calendar table and task dimension won't have full dates. Each year contains contiguous dates, but I'm sure the start and end dates won't be full years. 

I'll also have a go with  the code you've supplied once I'm in the office on Monday. Will also check out the link supplied. 
Really appreciate the advice, thanks again! 

[jdbuchanan71 · Accepted solution]

Hi @Anonymous 
Not haveing full years in your fact table (task dimension) is fine.  You just need to have full years in your date table and those dates must cover the whole range of your fact table.  This is so you can take advantage of things like SAMPERIODLASTYEAR() and DATESYTD, etc. and get correct behavior.