I am trying to get the data from excel sheet in which only FISCAL Period ID is given like
201506,
201507,
201411
I have applied the below logic
Cisco:
LOAD [End Customer Global Ultimate Name],
Country,
[End Customer HQ Top],
[Sales Level 1],
[Sales Level 2],
[Sales Level 3],
[Sales Level 4],
[Sales Level 5],
[Partner Name],
[Fiscal Period ID],
Year(Date#([Fiscal Period ID],'YYYYMM')) as Year,
Month(Date#([Fiscal Period ID],'YYYYMM')) as Month,
Date(Num(Date#([Fiscal Period ID],'YYYYMM')),'DD-MM-YYYY') as Date,
FMV,
[Net Bookings],
Group
FROM
[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Copy of June raw.xlsx]
(ooxml, embedded labels, table is Sheet1);
SET vFiscalYearStartMonth = 8;
LET vStartDate = Num(YearStart(Today(), -1));
LET vEndDate = Num(YearEnd(Today()));
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 CiscoYear, // Standard Calendar Year
Month(Date) AS CiscoMonth, // 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,
Date(Num(Date#([Date],'YYYYMM')),'DD-MM-YYYY') as FDate; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
Values are getting calculated but fields are not properly associated If I select the FiscalDate then values get de-associated and wrong calculations shows.But If I select the Fiscal Period ID then Correct Calculation is showing
