Only showing data based on Start date from a different calendar

[Jaden]

Hi,

I've been assigned a difficult task. We have 2 different calendars. One for Actuals and one for Estimated. Actuals shows the actual number of orders we have done and Estimated provides an estimated start date and estimated volume. We have an ID for each customer that's in my fact table. 

I currently use the Estimated start date and Estimated volume to distribute the volume over 12 months after the start date. For example, if our Estimated start date was 1/1/2025 and the Estimated volume was 12, I would get 1 order a month all the way until December 2025.

I currently use the Actuals calendar to get the Actual number of orders each month. Pretty straightforward on that one. 

We sometimes send orders to customers without having an Estimated start date. But we only want to track the orders 12 months after the Estimated start date. So, using the Estimated calendar I need to somehow select that Estimated start date and go only 12 months out from that Estimated start data for my Actuals table.

See this example below:

Expected(Estimated) runs from a start date of Feb 2024 - Jan 2024 (12 months) for this ID.

Actuals has data from Jan 2024 - Jun 2025 for this ID

I need to only show this for Actuals:

Basically I just need to line up my Actuals and Estimated values based on the start date from my Estimated calendar. Any advice would be much appreciated. Thank you.

[stevedark · Accepted solution]

Hi @Jaden 

You might find the P() function in set analysis useful here. It allows you to select values in one field based on selections in another, so something like:

sum({<[Actual Month]=P([Estimated Month]),[Estimated Month]=>}Values)

What you can also do with this approach is to have a data island (that is a table in the data load that is not connected to the rest of the data model) with a calendar and use that for selections and then apply those selections via a P() function.

sum({<[Actual Month]=P([Calendar Month])>}Values)

You can create the calendar using an AUTOGENERATE() statement:

LOAD
    [Calendar Date],
    Date(MonthStart([Calendar Date]), 'MMM-YYYY') as [Calendar Month],
    Year([Calendar Date]) as [Calendar Year]
    ;
LOAD
    Date(today()-(RecNo()-1), 'DD MMM YYYY') as [Calendar Date]
AUTOGENERATE(3650);

Hope that helps.

Steve