Considering Aug is the Start of Fiscal month, you can do something like below
Transaction:
LOAD Distinct
[Fiscal Period ID],
Year(date#([Fiscal Period ID],'YYYYMM')) as FiscalYear,
num(month(date#([Fiscal Period ID],'YYYYMM'))) as FiscalMonthNum
FROM
Sample_data.xlsx
(ooxml, embedded labels, table is Sheet1);
Left Join
LOAD * Inline [
FiscalMonthNum,CalenderMonth, CaldenderMonthNum
1,Aug,8
2,Sep,9
3,OCt,10
4,Nov,11
5,Dec,12
6,Jan,1
7,Feb,2
8,Mar,3
9,Apr,4
10,May,5
11,Jun,6
12,Jul,7 ];
Final:
LOAD *,
monthname(Date#(CalenderYear&CalenderMonth,'YYYYMMM')) as CalenderMonthYear,
date(Date#(CalenderYear&CalenderMonth,'YYYYMMM'),'DD-MM-YYYY') as Date;
LOAD
FiscalYear,
FiscalMonthNum,
CalenderMonth,
[Fiscal Period ID],
if(CaldenderMonthNum>=8,FiscalYear-1,FiscalYear) as CalenderYear
Resident Transaction;
DROP Table Transaction;
