Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi Everyone,
I have a fact table that is updated weekly with new dates that should then be reflected in the Date table as well. I am new to M and so I would appreciate any help to my below questions (I use the below M code from Enterprise DNA):
Thank you
let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) 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]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
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),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable
Solved! Go to Solution.
Once you have a Date table (from M or DAX), it doesn't matter. Both can be dynamically refreshed based on the dates in your data. Personally, I prefer DAX date table, as the code is easier for people to understand and update. In M, you can use Date.From(DateTime.LocalNow()) to get the current date on refresh and use the other Date functions to adjust that to get your desired end date.
Sounds like you have a DAX Date table, but here is the one I use in case it helps.
Date =
ADDCOLUMNS (
CALENDAR ( MIN ( Flights[FL_DATE] ), MAX ( Flights[FL_DATE] ) ),
//Relative dates with Min() Max() or absolute with Date(2019,1,1) format
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),
"YearMonthShort", FORMAT ( [Date], "YYYYmmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"WeekNumber", WEEKNUM ( [Date] ),
"YearWeekNum", YEAR ( [Date] ) & WEEKNUM ( [Date] ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "Q"
& FORMAT ( [Date], "Q" ),
"Working Day", IF ( WEEKDAY ( [Date] ) = 1 || WEEKDAY ( [Date] ) = 7, "N", "Y" ),
"Days from Today", DATEDIFF ( TODAY (), [Date], DAY ),
"Months from Today", DATEDIFF ( TODAY (), [Date], MONTH )
)
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Tara_ - I wrote an article last year on creating dynamic date tables in Power Query. Here is the link: Creating a Dynamic Date Table in Power Query
It is more efficient in PQ than in DAX as Power BI will treat it as a native data source vs calculated columns, which are not as efficient. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Tara_ - I wrote an article last year on creating dynamic date tables in Power Query. Here is the link: Creating a Dynamic Date Table in Power Query
It is more efficient in PQ than in DAX as Power BI will treat it as a native data source vs calculated columns, which are not as efficient. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you @edhans for the repsonse and the links, they were very informative. I am not planning on using calculated columns but perhaps a calculated table instead, and according to the last link you provided, Marco says:
"The worst compression is the one obtained by calculated columns, whereas the other two cases (calculated table and M query) have all native columns producing an identical compression." and "The memory required to process a calculated table depends on the number of rows and on the query plan of the DAX expression. Usually this is not a real issue for tables that have only tens of thousands of rows or less." .
The date table will not reach tens of thousands of rows so it should not be an issue in the long run. Also, I will take a look at the steps provided in your blog post and then decide which method would work well with the data. Thanks again.
Once you have a Date table (from M or DAX), it doesn't matter. Both can be dynamically refreshed based on the dates in your data. Personally, I prefer DAX date table, as the code is easier for people to understand and update. In M, you can use Date.From(DateTime.LocalNow()) to get the current date on refresh and use the other Date functions to adjust that to get your desired end date.
Sounds like you have a DAX Date table, but here is the one I use in case it helps.
Date =
ADDCOLUMNS (
CALENDAR ( MIN ( Flights[FL_DATE] ), MAX ( Flights[FL_DATE] ) ),
//Relative dates with Min() Max() or absolute with Date(2019,1,1) format
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYYMM" ),
"YearMonthShort", FORMAT ( [Date], "YYYYmmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"WeekNumber", WEEKNUM ( [Date] ),
"YearWeekNum", YEAR ( [Date] ) & WEEKNUM ( [Date] ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "Q"
& FORMAT ( [Date], "Q" ),
"Working Day", IF ( WEEKDAY ( [Date] ) = 1 || WEEKDAY ( [Date] ) = 7, "N", "Y" ),
"Days from Today", DATEDIFF ( TODAY (), [Date], DAY ),
"Months from Today", DATEDIFF ( TODAY (), [Date], MONTH )
)
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Yes @Tara_ - Calculated Columns and Calculated Tables in DAX are two very different things and the pros and cons of one do not apply to the other.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
23 | |
17 | |
14 | |
12 | |
11 |