What are date models?
Data models within Power BI are separate tables which include all the dates on a day level between a given minimum and maximum date. These can either be entered manually or relative to the source table’s date field. To provide some context this is very similar to the generate rows function within Alteryx. The purpose of which is to fill in missing dates within your source table in order to provide better context for the data you are handling.
data scaffolding is "a technique used to fill in pieces of data that are missing from your data source in order to help with analysis and visualization".
medium.com
This is also helpfully explained by the hospital bed issue analogy. Within any given hospital there are a fixed number of beds, and we needs to know how many are filled, and for how long. Within the dataset we may only have the days in which the beds have been occupied, and therefore only shows us one side of the story. If we scaffold the data by creating a date model this will fill in the gaps between these days and provide a complete picture in order to answer such questions as: How much of ‘X’ item to we need to account for on a daily basis, are there any trends within certain areas of over or under capacity and how can we account for this?
Within Power BI, there are two very effective ways of creating this model. The first is by using the ‘CalendarAuto’ function. This detects date fields within the source data and automatically creates a minimum and maximum value from which to base the date model. Helpfully you are also able to alter the fiscal month which can come in handy when working with different corporate entities utilising different calendars.
The second is by using the ‘Calendar’ function. This works in the same way although the user will have to manually enter the minimum and maximum date, either by entering ‘DD/MM/YYYY’ or by anchoring this onto an existing date field within the source data alongside a minimum and maximum function.
Crucially, it is important to mention the issue of data forecasting. With strict limitations on the minimum and maximum values within the date model you will have to account for future dates pre-emptively as the date model can only analyse data for the dates included.
Further manipulation of this model can be achieved by ‘splicing’ the date model you have created. Either by extracting the year, month, month number or weekday (to name a few), the user is able to create further columns alongside the day level model they have created.
An example of this syntax:
Date =
VAR MinDate = MIN(sales_data_sample[ORDERDATE])
VAR MaxDate = MAX(sales_data_sample[ORDERDATE])
RETURN
ADDCOLUMNS(
CALENDAR(MinDate, MaxDate)
, "Year", YEAR([Date]) , "Quarter", QUARTER([Date])
, "Month No", Month([Date]) , "Week No", WEEKNUM([Date])
, "Week Day", weekday([Date]) , "Month", FORMAT([Date], "MMMM")
, "Day of the Week", FORMAT([Date],"dddd"), "Day Name Short", FORMAT([Date],"DDD")
, "Quarter No", "Qtr "&FORMAT([Date],"Q"), "Year Month",FORMAT([Date], "YYYY-MMM")
, "Year Month Sort",FORMAT([Date],"YYYYmm")
, "Year Quarter", "Q" & FORMAT([Date],"Q") &", "& YEAR([Date])
, "Year Quarter Sort", FORMAT([Date],"YYYYQ")
, "Week Year", FORMAT([Date], "\WWW YYYY")
, "Week Year Sort", YEAR([Date])*100+WEEKNUM([Date]), "DateKey", [Date]
, "Days in Month", DATEDIFF(DATE( YEAR([Date]), MONTH([Date]),
1),EOMONTH([Date],0),DAY)+1
, "Start of Quarter",DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1)
, "End of Quarter",EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0)
, "Start of Week", [Date]-WEEKDAY([Date])+1
, "End of Week",[Date]+7-WEEKDAY([Date]))
Once the date model is created, the user will then have to create a ‘relationship’ with the source table. This becomes increasingly more important when considering the use of more than one table within a dashboard.
“A model relationship propagates filters applied on the column of one model table to a different model table.”
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
Suppose we have two tables, both with unique date fields which mean specific things within the context of that table. By creating a date model from the minimum date of one field to the maximum date of the other the user is able to create a single source of truth between the two. This can account for not only any differences within the range of dates that exist between the two tables, but also the specific days too.
Once the relationship is created, usually on the basis of a ‘one to many’, then the user is ready to visualise their data as they deem it necessary!