Forum Discussion
Update Date Table upon Scheduled Refresh
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
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
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
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.
