Fiscal Calendar MonthStart

[johngouws]

Good morning.

I am trying to define a Fiscal Calendar and have one field I cannot get to work correctly. That is the Fisc_MonthStart for January of the year. In the attached jpg file the field is empty but it should read ‘2021-12-26’.  

My fiscal year is from 26^th February to the following year 25^th February.

Fisc_MonthStart is defined as below.

if(month(FiscalDate) > 2,

MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),26)

,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),26))           as [Fisc_MonthStart]

I include the qvf to show what I have this far. I really appreciate a solution for this.

Thank you. 

[Lisa_P · Accepted solution]

Hi John,

The problem is that when you were doing this calculation and the month was January (1), subtracting 1 gave a 0.

So I added another clause to the if condition like this:

////Issue
,if(month(FiscalDate) > 2,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),26)
, if(Month(FiscalDate)>1, MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),26)
, MakeDate((Year(YearStart(FiscalDate, 0, $(FirstMonthOfYear)))+1), 1, 26))) as [Fisc_MonthStart]

and it appears to rectify your issue.

[johngouws]

Hi @Lisa_P , 

You are sooo clever.. I made one small change and I think it's perfect. It is now: 

,if(month(FiscalDate) > 2,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),26)
, if(Month(FiscalDate)>1, MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),26)
, MakeDate((Year(YearStart(FiscalDate,0, $(FirstMonthOfYear)))), 12, 26))) as [Fisc_MonthStart]

I checked other years and Jan is always correct. 

Thank you very much.