Get the last day of financial month

[#1]

Hi,

I am working with a financial calendar  where a month ends with the last full week (and weeks ending on Saturday) and I am trying to get the last day of the previous financial month.

Here is an excerpt from the MasterCalendar table:

Date          Weekday     Week     MonthYear

2013/01/26 Sat               5          Jan 2013

2013/01/27 Sun               6          Feb 2013

...

2013/02/23 Sat               8          Feb 2013

2013/02/24 Sun               9          Mar 2013

So for today (March 7th) I want to get the last day of the financial February: 2013/02/23.

Anyone has an idea how this can be calculated?

I also need the first day of the financial month two months ago (Jan 2013; should return 2012/12/30) but I think I can figure this out myself once the question above has been answered.

Thanks a lot in advance.

[Gysbert_Wassenaar]

See attached qvw. I recommend you calculate this in the script as in the attached example. But if you feel you must you can do this in an chart too with the same expressions.  For two months back use -2 in the addmonths function instead of -1.

[#3]

Hi Gysbert,

Thanks, this is great. I'm still trying to figure out how you are doing it, but I noticed that it does not work in some cases: for example March 2013 ends on 2013/03/30 in the fiscal calendar, whereas with your calculation it ends one week too early (23th).

Can you please have another look?

Thank you and regards,

Robert

[Gysbert_Wassenaar · Accepted solution]

I hope I've got the bugs out now. See attached qvw

[#5]

Hi Gysbert,

Thanks, it works flawlessly now.

It took me a while to understand how it works but now I have even found one litte optimization:

month(monthend(...)) as FiscalMonth

should be the same as

month(...) as FiscalMonth

Thanks again and regards.