Identify Last Day of the Month

Recently, when I was developing a business dashboard, I came across a nice trick when I needed to identify the last day of the month & the number of days in a month. Though it seems easier, It took some logic and I thought to share it with you.

Let's take an example of 3 dates, 14th, 28th, and 30th of January & February. This will cover almost all the scenarios. In the year 2020, the last day was 29th but in the year 2019, it was 28. So how to calculate it?

Simple.

1. Add 1 month to the date.

2. Take Date Trunc of that date (Because it gives 1st date of the desired portion month/quarter/year).

3. Reduce 1 day. So that we are back to the current date month and Last day. 

4. Bonus: Take Datepart of that date to identify the number of days in that month

We will use 3 functions to achieve this: DATEPART, DATEADD, and DATETRUNC. I am using Superstore Data for this example.

1. Add 1 month to the date

DATEADD('month',1,[Order Date])

2. Take the Date Truncate of the date.

DATETRUNC('month',DATEADD('month',1,[Order Date]))

3. Subtract one day to bring date back to current month

DATEADD('day',-1,DATETRUNC('month',DATEADD('month',1,[Order Date])))

Bonus:

4. Date part of the result date

DATEPART('day',DATEADD('day',-1,DATETRUNC('month',DATEADD('month',1,[Order Date]))))

This number is the number of days in the give date month. 

Your final calculation should look like this. Focus on how it calculated a different number of days for February 2019 & February 2020.

You can also identify the number of days in the current month by replacing [Order Date] with Today().