Help with Date table

[Joerobert]

Hello,

I don't really know the importance of date tables so I thought i would attempt to create one and apply to my data model as a trial run. Unfortunately i cannot get it properly set up. After obtaining various examples via google, i was able to create the following code in Power Query however I am noticing the following behavior:

1. The date column does not appear to generate for the entire year, as specified in the parameter

2. I am noticing some strange behavior with the data when attempting to sort

3. I am seeing a strange row count when i scroll down to the bottom of the table

My current data currently contains multiple tables that has multiple date/time columns where time is included with the date. As aresult, I am attempting to incorporate time in the date table so that I can relate the two tables for time intelligence functions.

//Create Date Dimension
(StartDate as datetime, EndDate as datetime)=>

let
//Capture the date range from the parameters
    StartDate = #datetime(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate),1,1,0),
    EndDate = #datetime(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate),1,1,0),

//Get the number of dates that will be required for the table
    GetDateCount = Duration.TotalMinutes(EndDate - StartDate),

//Take the count of dates and turn it into a list of dates
    GetDateList = List.DateTimes(StartDate, GetDateCount, 
    #duration(0,0,1,0)),

//Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

//Create various date attributes from the date column
//Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),

//Add Quarter Column
    QuarterName = Table.AddColumn(YearNumber , "Quarter Name", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

//Add Quarter Column2
    QuarterNo = Table.AddColumn(QuarterName , "Quarter No", 
    each Date.QuarterOfYear([Date])),

//Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNo , "Week of Year", 
    each Date.WeekOfYear([Date])),

//Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),

//Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month Name", 
    each DateTime.ToText([Date],"MMMM")),

//Add Day of Year Column
    Day = Table.AddColumn(MonthName, "Day", 
    each Date.Day([Date])),

//Add Day of Year Column
    DayOfYear = Table.AddColumn(Day, "Day of Year", 
    each Date.DayOfYear([Date])),

//Add Day of Week Column
    DayOfWeek = Table.AddColumn(DayOfYear , "Day of Week", 
    each DateTime.ToText([Date],"dddd"))
in
    DayOfWeek

[Greg_Deckler]

I put in 1/1/2017 and 12/31/2017 as my parameters. Seems like it stopped at 12/31/2017 @ 1:00 AM.

[Greg_Deckler]

That is a lot of work to create a calendar/date table when DAX has CALENDAR and CALENDARAUTO and a ton of other nice date functions like WEEKNUM, WEEKDAY, etc. Do you really need every minute of every day? I'll give your code a shot and see what I come up with though.

[Joerobert]

I was just playing with it again after posting and I was able to utilize in a report with only dates. I have date and time in my fact tables so I just thought I need to replicate it for the relationship.

You bring up a good point about the DAX functions, I guess I should just build up the basic table.

[Joerobert]

I am looking at this again, using a date table while carrying date/time in my fact table and I am only seeing a relationship with the rows where the timestamp ends in 12:00:00 AM. It does look like I might need to carry a timestamp in my date table and therefore carrying a duration for every minute.

[Anonymous · Accepted solution]

@Joerobert,

You can create a normal calendar table as Greg_Deckler's post, and create a date type field using dax in your fact table, then create one-to-many relationship between calendar table and fact table.

Do you get any issues using the above method?

Regards,
Lydia

[Joerobert]

Thanks Lydia, i acutally performed this very same task to get my issues resolved; just coverted the original column in my fact table to date type and I am now able to bring in all the rows through the relationship.

Thanks for your help!