I'm using the Canonical Calendar posted by Henric Cronstrom. (or let's say trying to use/implement)
But i'm facing some challenges.
I'm loading my sales data from a qvd. and I have 3 dates i'm interested in. Which have their own calendar. Invoice, order and Pick.
'Invoiced' as OrderStatus,
(Company & '-' & OrderType & '-' & OrderNumber & '-' & OrderLineNumber) as OrderLineID
Load OrderLineID, InvoiceDate as CalendarDate, 'Sales' as DateType
Load OrderLineID, OrderDate as CalendarDate, 'Order' as DateType
Load OrderLineID, PickDate as CalendarDate, 'Pick' as DateType
This all Works fine. Until i want to add my Budgets.
LOAD CalendarDate as InvoiceDate,
'BU' As Forecast_Type,
SalesBU as [Sales LCY BU],
MarginBU as [Margin LCY LOC BU],
Since in the ideal world the budget it perfect. It should always align with the invoicedate.. therefor there are no need for a forth calendar??
but when i load it. I can't see budget numbers after the highest invoice date. Even though i have forced in the calendar date til end of next year, in the calendar generator. There are budget numbers until end of next year. But i can't see them.
LET varMinDate = Num(MakeDate(2013,1,1));
LET varMaxDate = Floor(MakeDate(2018,12,31));
LET vDateToday = Num(Today());
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
I'm suspecting it due to the DateBridge. Which are then bridging all the 4 calendars. Main, order, invoice, pick. this bridge is all done from the Sales table. And it's somehow then limited to the highest dates from there.
What am i doing wrong? Do i really need to create one more Calendar for the forecasts/budgets? If so, how? I don't have an Unique OrderlineID. since budgets are done on customers total. not on orderlines.
If that is not the problem. Then what is it? I have also tried just loading it the budgets dates as CalendarDate. but this creates a loop.