Forum Discussion

Microsoft 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

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
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

Microsoft Employee

9 years ago

I haven't had a chance to trouble shoot this (i.e. start from scratch to make sure no errors are thrown) but try this as a way to not have to manually invoke this function after refresh (which would produce a new table, lose any data types and formatting, and would require you to re-establish a relationship between your dimDates table and Sales table).

After creating the function given in my original post, do the following:

Create table from function

Connect to new data source - "Blank Query"
Enter anything (i.e "1", it really doesn't matter)
Convert to table
Add Column -> Invoke Custom Function
Remove the original column from table, leaving only column with function name and value of Table
Expand the column with the double arrow button on header

Uncheck "Use original column headers as prefix"

Rename table as "dimDates"
Configure table (set data types, rename fields if wanted, etc.)

Close and Apply, then create relationship between dimDates table and relevant sales table using date field
You will need to apply some manual sorting rules under "Modeling" tab in order to get text representation of dates (ie.e month and day name) to displayed in correct order when used as axis in charts. Accomplish this using the appropriate numeric equivalent column

You now have a date dimension table and, as your sales database grows over time, these new dates will be added to the dimDates table automatically on refresh.

I will also test this for functionality after a publish to PowerBI service and share the results.

dkay84_PowerBI

Microsoft 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.

Anonymous

Not applicable

9 years ago

Something like below?

First Invoice Date = CALCULATE(MIN(Invoices[OrderDate]),ALL('Invoices'))
Last Invoice Date = CALCULATE(MAX(Invoices[OrderDate]),ALL('Invoices'))

blopez11
Super 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
  Microsoft 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
    Helper II
    9 years ago
    Yep, thanks for this. Very helpful. Eliminates the need for a future date flag as well. Cheers.

Forum Discussion

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

Related content

Dynamic Calculated Column as Visual dimension

degenerate dimensions

Dynamic Column headers

Dynamic Column Name

Dynamic Variable in Calculated Columns