DAY
Returns the day of the month of a date, from 1 to 31.
DAY(<date>)Returns: A whole number from 1 to 31.
What it does
DAY reads the day of the month out of a date. Finance teams use it for close calendars, for counting the days left in a month, and for testing whether a date falls in a certain part of the month, such as the first five business days. For counting days between two dates, DATEDIFF is the better function.
Syntax and arguments
- date: any date or datetime value. Required.
Example
A controller wants to show how many days are left in the current month on the close dashboard. The report date is the last date in the filter context. The measure takes the last day of that month and subtracts the report day.
Days Left in Month =
DAY(EOMONTH(MAX('Calendar'[Date]), 0)) - DAY(MAX('Calendar'[Date]))
If the report date is October 29, the last day of October is October 31, so the result is 31 minus 29, which is 2. On October 31 the result is 0. The EOMONTH function finds the month end, and DAY reads its day number.
Example: a second, different use
A close calendar marks the first five days of each month, when the team usually posts the prior month's entries.
Early Close Window =
IF(DAY('Calendar'[Date]) <= 5, "Close window", "Normal")
Things to watch
- DAY returns the day number only. The same number appears in every month, so pair it with MONTH and YEAR when a date must be unique.
- To count the days between two dates, use DATEDIFF with the DAY interval. Do not subtract DAY values, because that fails at month ends.
- DAY on a blank returns a blank, so check for missing dates before you use the result in a total.
- Business days skip weekends and holidays. DAY does not know about either, so use a workday flag in the Calendar for those counts.
In business terms
DAY supports close calendars, payroll cut-offs, bill run schedules and the countdown to month end. It is also used to flag late-month or early-month entries for review.