Forum Discussion
Change type of date table
- 4 years ago
Ok, here are two ways of creating a date table: one using Power Query and another using DAX.
1) In Power Query (bear in mind that the fact table in this case is a called fTable and the date field is fTable[Date])
Create a new blank query and paste in this code, adjusting the field names to date field name in your dataset.
let MinDataDate = List.Min(fTable[Date]), //Returns the minimum date in the fact table MaxDataDate = List.Max(fTable[Date]), //Returns the maximum date in the fact table #"MaxSalesDate1" = Date.AddDays(MaxDataDate, 1), //Adds one more day to the max date DayCount = Duration.Days(Duration.From(MaxSalesDate1 - MinDataDate)), //Counts the number of calendar days between the min and max dates Source = List.Dates(MinDataDate, DayCount, #duration(1, 0, 0, 0)), //Creates a list of dates, starting at the minimum date, to cover the range of dates in the fact table TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), //converts the list to a table ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}), #"Renamed Columns" = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}) in #"Renamed Columns"This creates a table of consecutive dates covering the range of dates in the fact table. Now you can simply add columns with different fields you need:
2) Date Table using DAX: the date field in the fact table is 'fTable'[Date]
Create a new table and use the following code.
DAX Date Table = VAR _MinDate = MIN ( fTable[Date] ) //Returns the minimum date in the fTable VAR _MaxDate = MAX ( fTable[Date] ) //Returns the maximum date in the fTable RETURN ADDCOLUMNS ( CALENDAR ( _MinDate, _MaxDate ), //Creates a table of dates between min and max dates. The new date field is [Date]. "MonthNum", MONTH ( [Date] ), //Creates a column with the month number "Month", FORMAT ( [Date], "MMM" ), //Creates a column with the month name "Year", YEAR ( [Date] ), //Creates a column with the year "Quarter", QUARTER ( [Date] ) //Creates a column with the Quarter number )To get the month name sorted in the correct order in visuals, order the date column in ascending order, select the month name and sort the column by the month number column:
Remember to mark the table as the date table as has been pointed out, and create the relationship between the corresponding date fields.
Sample file attached
Ok, here are two ways of creating a date table: one using Power Query and another using DAX.
1) In Power Query (bear in mind that the fact table in this case is a called fTable and the date field is fTable[Date])
Create a new blank query and paste in this code, adjusting the field names to date field name in your dataset.
let
MinDataDate = List.Min(fTable[Date]), //Returns the minimum date in the fact table
MaxDataDate = List.Max(fTable[Date]), //Returns the maximum date in the fact table
#"MaxSalesDate1" = Date.AddDays(MaxDataDate, 1), //Adds one more day to the max date
DayCount = Duration.Days(Duration.From(MaxSalesDate1 - MinDataDate)), //Counts the number of calendar days between the min and max dates
Source = List.Dates(MinDataDate, DayCount, #duration(1, 0, 0, 0)), //Creates a list of dates, starting at the minimum date, to cover the range of dates in the fact table
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), //converts the list to a table
ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(ChangedType, {{"Column1", "Date"}})
in
#"Renamed Columns"
This creates a table of consecutive dates covering the range of dates in the fact table. Now you can simply add columns with different fields you need:
2) Date Table using DAX: the date field in the fact table is 'fTable'[Date]
Create a new table and use the following code.
DAX Date Table =
VAR _MinDate =
MIN ( fTable[Date] ) //Returns the minimum date in the fTable
VAR _MaxDate =
MAX ( fTable[Date] ) //Returns the maximum date in the fTable
RETURN
ADDCOLUMNS (
CALENDAR ( _MinDate, _MaxDate ),
//Creates a table of dates between min and max dates. The new date field is [Date].
"MonthNum", MONTH ( [Date] ),
//Creates a column with the month number
"Month", FORMAT ( [Date], "MMM" ),
//Creates a column with the month name
"Year", YEAR ( [Date] ),
//Creates a column with the year
"Quarter", QUARTER ( [Date] ) //Creates a column with the Quarter number
)
To get the month name sorted in the correct order in visuals, order the date column in ascending order, select the month name and sort the column by the month number column:
Remember to mark the table as the date table as has been pointed out, and create the relationship between the corresponding date fields.
Sample file attached
You're amazing again!
Thank you a lot!