Change type of date table

[Michael_nik]

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(...)). So it automatically transformed as a Calendar type.

But then I asked a question on this forum and a person who solved my issue attached his own file, quiet similar to mine, but it didnt contain such type of date table - instead of it dates were performed as varaibles like this

 I tried to re-create the same calendar by inserting formula above (calendar = ...) but it didnt work, the result is not a calendar

I'm running out of ideas what to do: I tried to copy-paste tables from Power Query, I checked data types, marked table as Date - everything is similar except of the fact that in one workbook calendar is calender, whereas in the other - just a variable.

Thnx in advance, Mike

[PaulDBrown · Accepted solution]

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

[Michael_nik]

You're amazing again!

Thank you a lot!

[truptis]

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.

[Michael_nik]

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.

[Shaurya]

Hi @Michael_nik,

Let me start by pointing out that there is nothing wrong with the date table that you have and infact, it is preferred to use it in the second format.

However, if you do want to go back to the first one, just unmark the table as date table.

Once you do this, you'll see the values back in the original way.

Works for you? Mark this post as a solution if it does!

[Michael_nik]

Thank you for your reply! 
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.

[PaulDBrown]

Once you have a Date table, you won't see a "hierarchy"; you create the hierarchy by adding the fields you need from the date table to the row/columns in the visual.