Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tara_
Helper II
Helper II

Update Date Table upon Scheduled Refresh

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

 

  1. How can I update the code below (or if u suggest another code) to accept dynamic end dates, the date table should contain the most recent dates when the fact table is refreshed through the Power BI service (fact table stored in SQL Server). 
  2. For the start date which is better, should I keep it as a parameter or hard code it with the first date in the fact table?
  3. In this case, how can the date table be refreshed once the fact table is refreshed? Will it be done automatically upon scheduled refresh of the data set? 
  4. I wrote a dynamic date table in DAX and it works fine (I know Power Query is better), but based on your experience what are the long term implications of using the DAX custom table in this case?

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

 

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you @mahoneypat for your response and the DAX Date Table. 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors