Current Fiscal Month Number

Anonymous — Tue, 26 Sep 2017 10:07:40 GMT

Can anyone let me know how to return the current Fiscal Month Number based on todays date.

26 September 17 = 6

14 May 17 = 2

Etc

Many thanks

Phil

Re: Current Fiscal Month Number

Anil_Babu_Samineni — Tue, 26 Sep 2017 10:22:22 GMT

arvind_patil — Tue, 26 Sep 2017 10:34:06 GMT

Hi Phil,

Please Find below Script:

It will work For you:

SET vFiscalYearStartMonth = 4;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(AddYears(Today(),1));

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Thanks,

Arvind Patil