March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 have code to create a dimDate table but the user has to manually enter start and end dates. Similarly, I have two functions that, when invoked, return the earliest and most recent date, respectively. Can anyone give me some tips on how to integrate these?
Below is the code I am using (I did not create this) to generate a user defined dimDate table. Is it possible to call the functions that return the start date and end date instead of requiring the user to hard code these in?
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table => let 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", Culture), 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", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date) in InsertWeekEnding in CreateDateTable
Solved! Go to Solution.
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"
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" )
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"
Thanks for this post, I have looked for such as well
A couple of questions
Does this dynamically update as dates in your Invoices table change? Meaning, if the max date in your invoice table changes based on data refresh, will the dates in the date table reflect this? Also, how would this be called in such a scenario
Thanks,
Yes this dynamically updates. Notice near the beginning of the code we have two declarations:
StartDate = List.Min(Table.Column(Invoices,"OrderDate"))
EndDate = List.Max(Table.Column(Invoices,"OrderDate"))
To set this up with your own data, after connecting to your data, open the query editor. Create a new blank table. Select the table from the list of queries and go to Advanced Editor. Replace the existing code with the code from this post and it should become a function. Then, to invoke, simply select the function and look for the button "invoke".
Got it, so you have to invoke the function each time you want it updated
Appreciate the response
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:
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.
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.
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" )
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.
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
The Duration.Days step can be changed to hours or mins (https://msdn.microsoft.com/en-us/library/mt296613.aspx)
Everything else should be more or less the same. The step to create a list of all time periods would change from List.Days to List. something else depending on your granularity (https://msdn.microsoft.com/en-us/library/mt296612.aspx)
Yep, thanks for this. Very helpful. Eliminates the need for a future date flag as well. Cheers.
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.
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?
Something like below?
First Invoice Date = CALCULATE(MIN(Invoices[OrderDate]),ALL('Invoices')) Last Invoice Date = CALCULATE(MAX(Invoices[OrderDate]),ALL('Invoices'))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
86 | |
75 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
55 |