I'm trying to tackle a requirement in AAS (Azure Analysis Service) tabular model.

In the underlying data model (specific to a particular application), I've a Sales Fact table which joins with multiple dimension tables (lets call these application specific dimension tables) using usual PK-FK relationships.

• These application specific dimension are frozen for any changes in DWH, at the end of reporting month-year and hence can be considered to have snapshots for all reporting month-years.
• Each of these dimension tables has a corresponding "enterprise" version which is a Type 2 dimension table. That is, for a dimension such as Product, the enterprise version of the stores the history of changes for dimension attributes with usual fields such as valid-from date (start of the month-year), valid to-date (end of the month date) and latest indicator ('Y' value for the latest version of a specific reporting month-year) . An new attribute value means a new Product FK.

Both versions of these dimension tables and the Sales fact would be part of the AAS tabular model.

Now, as part of the reporting requirement I need to build a report (over this tabular model) running for a particular reporting month-year and show Sales data along with specific dimension attribute for the month-year. Additionally, the report needs to show the data of these dimension attributes from the "enterprise version" of the dimension tables as well, with the latest values (Latest indicator='Y').

For example

Considering just one set of dimension tables

Product-App (Product Name, Product Group, Product PK .... so on) Product-Enterpise (Product Name, Product Group, Valid-From, Valid-To, Latest Indicator ... so on)

and the Sales Fact :-

**if the user is running the report for Nov-2021 and running this in Jan-2022, the report needs to show Nov Sales data with

1. Product attributes from Product-App and
2. Latest values of the Product attributes from Product-Enterprise That is; for point 2 requirement, the tabular model needs to provide a way for the user to bring the latest values of the Product dimension attributes from Product-Enterprise.**

My understanding is: such capability would mean that to bring the latest values of specific dimension attribute in this scenario/data model, a calculated column or measure approach would need to be taken. Either a calculated column: to map and get the latest values of dim attributes in application-specific dimension or a measure. These calculated columns and measure would then can be consumed from the model for reporting.

I'm looking to any pointers if this is the best approach or are there any alternate approaches can be taken to best address such a requirement.

Thanks!