Forum Discussion

Michael_nik's avatar
Michael_nik
Regular Visitor
3 years ago
Solved

Change type of date table

Hi everyone!   Recently, I stucked with a problem: I downloaded a sample dataset called 'Financials' from PowerBI and created a calendar table by simple formula "Calendar = Calendar(date(...), date...
  • PaulDBrown's avatar
    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