Creating a starter file for new Power BI data models is a beneficial practice if you frequently build such models. Instead of starting from scratch each time, you can save time by using a starter file that has certain elements already set up. This can include pre-loading commonly used custom visuals, themes, and other resources. Additionally, you can include images, backgrounds/wallpapers, and pre-made functional elements such as a collapsible slicer panel, as demonstrated on Guy In A Cube recently. A few other elements that you might consider including in your starter file are explained in more detail below.
1. DAX Date Table
Having a Date table is essential for any data model that includes date columns. Creating a Date table can be done easily using DAX or an M query. One approach that I often use is shown below. While I may modify the Calendar() part depending on the specific model I’m building, the overall structure remains the same. I typically use it as is and reference the main date column in the data, so that the Date table automatically adjusts to the date range in the data upon refresh.
You can also define a fixed date range, such as “Calendar(Date(2016,1,1), Date(2019,12,31))”, or use a hybrid approach like “Calendar(Date(Year(Min(Table[DateColumn])),1,1), Date(Year(Max(Table[DateColumn])),12,31)”. The hybrid approach covers the period from the earliest year to the latest year in the data.
Date =
ADDCOLUMNS (
CALENDAR ( MIN ( Table[DateColumn] ), MAX ( Table[DateColumn] ) ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),
"YearMonthShort", FORMAT ( [Date], "YYYYmmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"WeekNumber", WEEKNUM ( [Date] ),
"YearWeekNum", YEAR ( [Date] ) & WEEKNUM ( [Date] ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "Q"
& FORMAT ( [Date], "Q" ),
"Working Day", IF ( WEEKDAY ( [Date] ) = 1 || WEEKDAY ( [Date] ) = 7, "N", "Y" )
)2. Measures table
Another recommended practice is to create a table specifically for storing all the measures you create in your Power BI model, making it easier to locate them. While you can easily create a table by entering a “1” through “Enter Data”, I prefer to create a blank query called “1Measure” (so it appears at the top of my list of tables) and enter a simple expression to capture the datetime of the last refresh. I then add this as a card on the first/summary report page.
To create this table, you can enter the expression “=DateTime.LocalNow()” in the blank query, convert it to a table, and rename it to “Last Refreshed”. Finally, you can change the column type to DateTime. If you prefer, you can directly copy and paste the M expression below into a blank query:
let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refreshed"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refreshed", type datetime}})
in
#"Changed Type"Another option to obtain the last refresh date is by using the DAX expression “Now()” in a calculated column. For instance, if you create a measures table using the “Enter Data” approach with a value of “1”, you can simply add a calculated column with the expression “Last Refresh = Now()”. This calculated column will automatically update with the current date and time whenever data is refreshed in your Power BI model. This provides an easy way to track the last refresh date directly within your measures table.
DataCat