Forum Discussion

dkay84_PowerBI's avatar
dkay84_PowerBI
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago
Solved

Date Dimension Table that Dynamically Pulls Start and End dates from a Column of Dates

As the title suggests, I am looking to create a date dimension table with M that checks for the earliest and most recent invoice dates and creates a date table from that with 1 day increments.  I hav...
Need Help
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    As is typical in these types of challenges, I spent a while scouring the internet and trying to get a solution before I posted my question here, only to arrive at one a few minutes later...

     

    Anyway, here is the revised code that can be used:

    let CreateDateTable = () as table =>
  let
    StartDate = List.Min(Table.Column(Invoices,"OrderDate")),
    EndDate = List.Max(Table.Column(Invoices,"OrderDate")),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
  in
    InsertWeekEnding
in
  CreateDateTable

    The red text is what you would need to customize, where, in my example, the table name is "Invoices" and the invoice date column is "OrderDate"

  • blopez11's avatar
    blopez11
    9 years ago

    This is route I ended up using since I wanted the table updated when I did a refresh

    I found this from another post, so I can't take any credit here

     

    DateTable =
    ADDCOLUMNS (
    CALENDAR (MINX(HR_HEADCOUNT,[Date]), NOW()),
    "Year", YEAR ( [Date] ),
    "QuarterOfYear", FORMAT ( [Date], "Q" ),
    "MonthOfYear", FORMAT ( [Date], "MM" ),
    "DateInt", FORMAT ( [Date], "YYYYMMDD" ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
    "QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
    "DayInWeek", WEEKDAY ( [Date] ),
    "DayOfWeekName", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" )

dkay84_PowerBI's avatar
dkay84_PowerBI
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Additionally, you can create a calculated table (under "modeling" tab), using the dax expression Calendar(start date, end date), where start and end date are calculated using dax (min and max of sales[dates] while ignoring filters).  From here, you will have to build out the steps the M code above performed, and configure data types and formatting, and create the relationship between this dates table and the sales table.

 

This way seems much easier, not sure why I couldn't find this out earlier in my search.  At the moment, I don't have the specific dax expression to calculate min and max while ignoring applied filters.

blopez11's avatar
blopez11
Icon for Super User rankSuper User
9 years ago

This is route I ended up using since I wanted the table updated when I did a refresh

I found this from another post, so I can't take any credit here

 

DateTable =
ADDCOLUMNS (
CALENDAR (MINX(HR_HEADCOUNT,[Date]), NOW()),
"Year", YEAR ( [Date] ),
"QuarterOfYear", FORMAT ( [Date], "Q" ),
"MonthOfYear", FORMAT ( [Date], "MM" ),
"DateInt", FORMAT ( [Date], "YYYYMMDD" ),
"MonthName", FORMAT ( [Date], "mmmm" ),
"MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
"QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
"DayInWeek", WEEKDAY ( [Date] ),
"DayOfWeekName", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" )

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    This is a much more elegant solution that is also definitely going to work after publish, as all columns are calculated via DAX.  Thanks for finding this.

  • saunders's avatar
    saunders
    Icon for Helper II rankHelper II
    9 years ago

    Yep, thanks for this.  Very helpful.  Eliminates the need for a future date flag as well.  Cheers.

  • ovetteabejuela's avatar
    ovetteabejuela
    Icon for Impactful Individual rankImpactful Individual
    9 years ago

    I have a little problem with the accepted solution.

     

    It doesn't return the maximum date, but instead it return Maximum Date - 1, is this the culprit: 

     

                  DayCount = Duration.Days(Duration.From(EndDate - StartDate)),

     

    If it is, how do I correct it, +1?

     

    Thanks for the assistance in advance.

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago
    StartDate = List.Min(Table.Column(Invoices,"OrderDate")),
EndDate = List.Max(Table.Column(Invoices,"OrderDate")),
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),

    I'm not sure where your problem is coming from.  If the StartDate = 1/1 and the EndDate= 1/10, then DayCount would be 9.

     

    Then, Source would be a list of dates starting on 1/1 and running for 9 days, which would make the last date 1/10.

     

    What is your start and end dates?  Maybe their is a leap year aspect that is not accounted for?

  • ppfisterer's avatar
    ppfisterer
    Frequent Visitor
    8 years ago

    This is great for Dates.  Is there something similar for TIME?

    I need to find data associated with a specific short time period, usually 30 minutes to a couple of hours.  The start and end times of the period are stored in one table and the data is in another. 

     

    Any ideas?

     

    Thank you,

     

    Phil