LAST_DAY() function

[jovanpop-msft]

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;

[Drsql]

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.

[Shahid12523]

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

[TwinkleCyril]

Thanks for the feedback. We have added this feature to our backlog.