Linking Dimensions across different models to pick the current master data status

[GavinMonteiro3]

Hi All,

In SAP Analytics Cloud (SAC) Planning, we have a requirement to fetch the store status based on the date filtered in the story.

In the below example, Store A is under renovation for Period 1 of 2024 to Period 3 of 2024
Store B is closed in Period 4
Store C was Already Open
Store D is a new store opened in Period 2 of 2024.

User will maintain the data in a custom table

Hence, the requirement is that lets assume we are in period 9 of the 2024.

If a person runs the period filter in the dashboard from Period 1 to Period 4, the below store status should be visible in the table, based on the data mentioned in the table

Store	Status
A	Renovation
B	Closed
C	Open
D	New

In the transaction data being sent to SAC, we have the store code & the posting date being sent.

We do not want to load this store status on the transactional data, since business might change the store status for backdated dates.

E.g., in July 2024, they might change the status of a store of Jan 2024.

Hence, based on the data in the table, we want the system to use a similar formula like VLOOKUP, where system would pick the status mentioned in the above table based on the store code & the period combination.

@N1kh1l if you could please provide any inputs in achieving this.

Regards,

Gavin.

[chmbilal]

Creat custom  dimension table that holds the store status information. This table should include columns for the store code, period, and status.
In Model please ensure that the table is linked to the relevant dimensions, such as Store Code and Date (Period).

Use the story filter for the period to dynamically adjust the LOOKUP() formula. The formula will fetch the appropriate store status based on the period(s) selected by the user.

Create a calculate measure and use 
LOOKUP([d/StoreCode], [d/Period] = CurrentPeriod(), [d/StoreStatus])
Where current period() is the period selected in the story filter
and  when multiple periods are selected, you can write a more complex formula
IF(
[d/Period] >= StartPeriod() AND [d/Period] <= EndPeriod(),
LOOKUP([d/StoreCode], [d/Period] = CurrentPeriod(), [d/StoreStatus]),
"Open" // Default or fallback status
)
Hope you understand this.
let me know if you need more input