Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Last day of prev month where it has value.

Hi All ,

How to get the Last day of previous month where it has value . say for example. I did get the last day of previous month but i.e 31st of aug. but there is no data for 31st aug. the last day is 30 th of aug where it got day.

How can I pick up the last day of prev month where we have data.

My script in under "Flagfields" : 

F if("Statement Date" = Date(Monthend(Addmonths('$(MAX_STATEMENT_DATE)',-1)), 'YYYY-MM-DD'), 'Opening Balance '& Day(Monthend(Addmonths('$(MAX_STATEMENT_DATE)',-1)) ) & Month(addmonths('$(MAX_STATEMENT_DATE)',-1)),


sample model attached.

1 Reply
Partner - Specialist
Partner - Specialist


if you want to do this inside the script you could do something like this:

1) Where you load the table with all the dates, create two new fields:


Month(AddMonths(date,-1)) & Year(AddMonths(date,-1)) as LastMonth

Month(date) & Year(date) as Month

2) Then resident the table and use join


left join


Month as LastMonth,

max(date) as max_date_last_month

Resident Table

group by Month;