Fabric DW currently supports the EOMONTH() function, which returns the last day of a given month. However, it does not provide a built-in function that can return the last day of a week, quarter, or year in addition to the month.
Calculating the last day of a week, quarter, or year requires custom expressions using combinations of DATEADD, DATEDIFF, and calendar logic. This adds complexity and reduces readability in time-based reporting and analytics.
The LAST_DAY() function is supported in several other databases Snowflake, MySQL, Oracle, and makes calculating the last dat in interval much easier:
LAST_DAY('2025-08-18', 'MONTH') -- → 2025-08-31
LAST_DAY('2025-08-18', 'QUARTER') -- → 2025-09-30
LAST_DAY('2025-08-18', 'YEAR') -- → 2025-12-31
LAST_DAY('2025-08-18', 'WEEK') -- → 2025-08-23
The equivalent expressions in Fabric DW are:
SELECT EOMONTH(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, '2025-08-18'), 0)) AS LastDayOfQuarter;
SELECT EOMONTH(DATEFROMPARTS(YEAR('2025-08-18'), 12, 1)) AS LastDayOfYear;
SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, '2025-08-18'), '2025-08-18') AS LastDayOfWeek;
3 Comments
- Nice function idea. Do first_day as well. And it must honor a first day of the week setting as well. If you could set custom ranges it becomes worth its weight in gold, but the date table needs a job still I guess.
- In Fabric DW, EOMONTH() works only for month-end. For other intervals, you need custom expressions: Week: DATEADD(DAY, 7 - DATEPART(WEEKDAY, date), date) Quarter: EOMONTH(DATEADD(QUARTER, DATEDIFF(QUARTER, 0, date), 0)) Year: EOMONTH(DATEFROMPARTS(YEAR(date), 12, 1))
- Thanks for the feedback. We have added this feature to our backlog.
