dynamic month dimension

[Manel222]

Hello, 

I tried to create a dynamic date dimension that generates months between strat date and end date.

I tried this script, didn't work, any help please

TempMonths:

LOAD

    Date(MonthStart(AddMonths(Date#($(vDateDebut), 'DD/MM/YYYY'), IterNo()-1)), 'MMM YYYY') as MonthYear

AutoGenerate 1 While IterNo() <= $(vNbMois);

Months:

LOAD Distinct

    MonthYear

Resident TempMonths;

DROP Table TempMonths;

[anat]

1:
LOAD
Start_Date,
End_Date,

FROM
table;

Link_Table:
LOAD min(Start_Date) as minDate,
max(End_Date) as maxDate
Resident 1;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Link_Table;

TempCalendar:
LOAD
$(vMinDate) + IterNo() - 1 as Num,
Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:
LOAD
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
'Q' & ceil(month(TempDate) / 3) as Quarter,
'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,
MonthName(TempDate) as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear
Resident TempCalendar
Order By TempDate ASC;
DROP Table TempCalendar;

[Manel222]

@anat thanks but the script :

Let v_Date_fin date(Today());
Let v_Date_Debut=date(MonthStart(today(),-24));
// Calculer le nombre de mois entre les deux dates
TempCalendar:
LOAD
$(v_Date_Debut) + IterNo() - 1 as Num,
Date($(v_Date_Debut) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(v_Date_Debut) + IterNo() -1 <= $(v_Date_fin);
MasterCalendar:
LOAD
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
'Q' & ceil(month(TempDate) / 3) as Quarter,
'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,
MonthName(TempDate) as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear
Resident TempCalendar
Order By TempDate ASC;
DROP Table TempCalendar;

it returns one records 30/12/1899

[TauseefKhan]

Hi @Manel222,

Required Result:

Data:
LOAD * INLINE [
ID, StartDate, EndDate
1, 01/01/2023, 31/12/2023
2, 15/02/2023, 14/11/2023
3, 10/03/2022, 20/09/2022
];

LET vDateDebut = Date(Min(Date#(StartDate, 'DD/MM/YYYY')));
LET vDateFin = Date(Max(Date#(EndDate, 'DD/MM/YYYY')));

LET vNbMois = 12 * (Year(vDateFin) - Year(vDateDebut)) + Month(vDateFin) - Month(vDateDebut) + 1;

TempMonths:
LOAD
Date(MonthStart(AddMonths(Date#('$(vDateDebut)', 'DD/MM/YYYY'), IterNo() - 1)), 'MMM YYYY') as MonthYear
AUTOGENERATE 1 WHILE IterNo() <= $(vNbMois) +1;

Months:
LOAD DISTINCT
MonthYear
RESIDENT TempMonths;

DROP TABLE TempMonths;

DataWithMonths:
LOAD
ID,
StartDate,
EndDate,
Date(MonthStart(AddMonths(Date#(StartDate, 'DD/MM/YYYY'), IterNo() - 1)), 'MMM YYYY') as MonthYear
RESIDENT Data
WHILE AddMonths(Date#(StartDate, 'DD/MM/YYYY'), IterNo() - 1) <= Date#(EndDate, 'DD/MM/YYYY');

DROP TABLE Data;

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[Manel222]

Hello the variable NBmois is empty