5 Replies Latest reply on Mar 6, 2018 10:07 AM by james.diaz

# Formula to identify last day of month

below formula identifies the last day of every month:
DATEADD(‘day’,-1,DATEADD(‘month’,1,DATETRUNC(‘month’,[Date]))) 1}
Can anyone explain what exactly each above words in formula do/ functions ?

and is above formula correct ?

• ###### 1. Re: Formula to identify last day of month

Hi Varun

Something like

{fixed [Order Date (Month / Year)]:max([Order Date])}

will do...

Regards,

Norbert

• ###### 2. Re: Formula to identify last day of month

Hi Norbert,

can you please explain your formula ? In detail...

Thanks!

• ###### 3. Re: Formula to identify last day of month

DateTrunc gives you first date of Month like 1/1/2018. Now u are using DATEADD and adding one month, so it is returning 2/1/2018. Now You are again using DATEADD and substracing 1 day from Result so it is giving you 1/31/2018

Thanks

Deepak

• ###### 4. Re: Formula to identify last day of month

Hi Varun,,

1. "Aggregate" Order Date based Custom Date to "Month/Year"

2. D1. Last day of the month: {fixed [Order Date (Month / Year)]:max([Order Date])} // find for each month the last date

LOD expressions are explained here

Regards,

Norbert

• ###### 5. Re: Formula to identify last day of month

you can use this formula:

datepart('day',DATEADD('day',1,[yourdate]))=1

how: adds a day to your date, and then tells you if the date +1 day = the 1st of the month (following), in which case, the date must be the end of the month.