Forum Discussion
Change type of date table
- 3 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
Hi Michael_nik ,
You can delete the date hierarchy else set the Date table as "mark as date table"
For that, right click on your date table you will see the option as "mark as date table"
Michael_nik -> please hit the thumbs up - mark this as a solution if it helps you. Thanks.
Thank you for your reply!
As I said below: There is one problem - I already tried it and instead of the desired result, I got only a strange icon that doesn't look like a calendar 😞
As you can see - there is no calendar and no hierarchy.