Fiscal Calendar Linked With Sales Fact table against Ordered Date not showing proper Values

[msmahmood]

Please help.

SET vFiscalYearStartMonth = 7; //First month of the fiscal year(July)

LET vStartDate = Num (YearStart (Today(), -6)); //Jan-2017

LET vEndDate = Num(YearEnd(Today(), -1)); //Dec-2023

FiscalCalendar_D:
//3. Create Fiscal Quarter and Month Name`

Load

*,
dual('Q' & ceil(FiscalMonth/3), ceil(FiscalMonth/3)) As FiscalQuarter,

dual (text(SubField(Monthname(ORDERED_DATE_C), ' ',1)), FiscalMonth) as FiscalMonthName
;

//2. Crate Fiscal Month and Year
Load
*,


mod(
(month(ORDERED_DATE_C) - $(vFiscalYearStartMonth))
,12)
+ 1
as FiscalMonth
, YearName(ORDERED_DATE_C , 0, $(vFiscalYearStartMonth)) as FiscalYear_C
//, Year(Date(AddMonths(ORDERED_DATE_C,6))) as SO_FISCAL_YEAR // commented 08MAR2023

;

//1. Create fiscal calendar records
Load
date($(vStartDate) + IterNo() - 1) as ORDERED_DATE_C //ORDERED_DATE
AutoGenerate 1
while ($(vStartDate) + IterNo() -1) <= $(vEndDate)
;

This is my fiscal calendar and it's showing the correct values.

I've linked it in the fact table using ORDERED_DATE_C field.

In the Fact Table I've created two fields against ORDERED_DATE one is ORDERED_DATE and the second one is ORDERED_DATE_C.

When I display the fiscal calendar it's showing all values correct in the fields displayed like FiscalQuarter, FiscalMonthName etc.

But when I Add the field ORDER_NUMBER from Fact Table with those fields it only shows few values in the ORDER_NUMBER filed while Orders are there against all ORDERED_DATE_C.

For your reference I'm attaching the output as excel file.

Please guide.

Thanks