Automatically ranged date table using DAX and M

I am prepping some demo’s for my SQLPass summit session “End-to-End Demos with Power BI” where I’ll show many of the tricks I use in my Power BI workbooks.

One of them is this trick that I recently figured out, I am so excited about it that I wanted to share it with all of you. I figured out a way to automatically generate a data table with a dynamic range based on the data in the fact tables.

Let’s take this table I created in Excel (and loaded into PowerPivot:

Now I want to create a data table in Power Pivot that would create a data table from 1/2/2013 to the end of the year. When new data comes in the table range should automatically change. First thing I did was find a way to determine the MIN and MAX data value in the range. For this I used a DAX query. To do this, click on existing connections in Excel, select tables and double click the table.

Now Excel asks how what you want to do with this data, select create a table:

Now Excel created a table that shows all rows in the table, under the covers this table is generated by using DAX. Excel allows you to change the DAX:

Here select DAX and use the following DAX query:

EVALUATE
ROW(“MAX”,max(Table1[date]) , “MIN”, min(Table1[date]) )

This will create a table with the MIN and MAX date in the fact table, the great thing here is that these dates are part of the recalc change. When new data gets loaded into the fact table this query will be run and the table will get updated with the min and max dates based on the data in the table:

Now we can start to create the date table, I use this function that Chris Webb blogged about earlier to generate a date table based on a start and end date:

let
CreateDateTable = (StartDate, EndDate) =>
let
//StartDate=#date(2010,1,1),
//EndDate=#date(2014,12,31),

//Create lists of month and day names for use later on
MonthList = {“January”, “February”, “March”, “April”, “May”, “June”
, “July”, “August”, “September”, “October”, “November”, “December”},
DayList = {“Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”},

//Find the number of days between the end date and the start date
NumberOfDates = Duration.Days(EndDate-StartDate),
//Generate a continuous list of dates from the start date to the end date
DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

//Turn this list into a table
TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}
, null, ExtraValues.Error),
//Caste the single column in the table to type date
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Date”, type date}}),

//Add custom columns for day of month, month number, year
DayOfMonth = Table.AddColumn(ChangedType, “DayOfMonth”, each Date.Day([Date])),
MonthNumber = Table.AddColumn(DayOfMonth, “MonthNumberOfYear”, each Date.Month([Date])),
Year = Table.AddColumn(MonthNumber, “Year”, each Date.Year([Date])),
DayOfWeekNumber = Table.AddColumn(Year, “DayOfWeekNumber”, each Date.DayOfWeek([Date])+1),

//Since Power Query doesn’t have functions to return day or month names,
//use the lists created earlier for this
MonthName = Table.AddColumn(DayOfWeekNumber, “MonthName”, each MonthList{[MonthNumberOfYear]-1}),
DayName = Table.AddColumn(MonthName, “DayName”, each DayList{[DayOfWeekNumber]-1}),
IsThisWeek = Table.AddColumn(DayName, “IsThisWeek”, each Date.IsInCurrentWeek([Date])),
//Add a column that returns true if the date on rows is the current date
IsToday = Table.AddColumn(IsThisWeek, “IsToday”, each Date.IsInCurrentDay([Date])),
IsThisYear = Table.AddColumn(IsToday, “IsThisYear”, each Date.IsInCurrentYear([Date])),
WeekEnding = Table.AddColumn(IsThisYear , “Week Ending”, each Date.EndOfWeek([Date])),
#”Removed Columns” = Table.RemoveColumns(WeekEnding,{“IsToday”}),
#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“DayOfMonth”, Int64.Type}, {“MonthNumberOfYear”, Int64.Type}, {“Year”, Int64.Type}, {“DayOfWeekNumber”, Int64.Type}})
in
#”Changed Type”
in
CreateDateTable

Now all we need  to do is get the min and max values from the Excel table to be used as parameters for the function.  We can use the new Power Query function “Excel.CurrentWorkbook()” to get data from any Excel table. I made sure I named the table appropriately so it can be used easily in the function call that gets the actual data “Source{[Name=”tblDateRange”]}[Content]”. Now I use PQ to get the year value of the MAX value and change that to be month 12 and day 31. Now the min and max data value can be passed into the date table function by referencing the columns in the PQ table and referencing into it: “Table[MinDate]{0}” will get the value for the MinDate value for the first row.

let
Source = Excel.CurrentWorkbook(),
tblDateRange = Source{[Name=”tblDateRange”]}[Content],
#”Duplicated Column” = Table.DuplicateColumn(tblDateRange, “MAX”, “Copy of MAX”),
#”To Year” = Table.TransformColumns(#”Duplicated Column”,{{“Copy of MAX”, Date.Year}}),
#”Renamed Columns” = Table.RenameColumns(#”To Year”,{{“MAX”, “OldMAX”}, {“Copy of MAX”, “MaxYear”}}),
#”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Custom”, each #date([MaxYear],12,31)),
#”Renamed Columns1″ = Table.RenameColumns(#”Added Custom”,{{“Custom”, “MaxDate”}, {“OldMAX”, “MAX”}, {“MIN”, “MinDate”}}),
#”MinDate” = Table.TransformColumnTypes(#”Renamed Columns1″,{{“MinDate”, type date}}),
#”Table” = Table.TransformColumnTypes(#”MinDate”,{{“MaxDate”, type date}}),
DateTable = fnDateTable(Table[MinDate]{0},Table[MaxDate]{0})
in
DateTable

This creates a data table that uses the Min and Max date returned from the DAX function:

Hope this gives you something you can use.

Download the workbook here to play with it yourself: https://onedrive.live.com/redir?resid=7F4E0559CC74581A!158118&authkey=!AIFBwJG3oGrrIIw&ithint=file%2cxlsx