Data Modeling Validation: Should Semi-Additive Measures Always Be in a Separate Fact Table?

Thankyou, TomMartens, for your response.

Hi Mahhin_Shahzad1,

We appreciate your inquiry submitted to the Microsoft Fabric Community Forum.

Based on my understanding, the issue arises because semi-additive or balance sheet measures such as Assets, Liabilities, and Inventory behave differently from fully additive or P&L measures such as Sales and Expenses. While P&L values can be summed across time periods, balance sheet values are point-in-time measures and therefore require functions like LASTDATE() or LASTNONBLANK() for correct aggregation. Combining both types of measures in a single fact table increases aggregation complexity, complicates DAX logic maintenance, and may lead to performance overhead.

As a best practice, it is advisable to keep semi-additive measures (balances) in a separate fact table (for example: FactMonthlyBalances) and fully additive measures in another fact table (for example: FactMonthlyFlows). Both tables can share the same Date, Company, and Measure dimensions to facilitate cross analysis. This design ensures accurate time aggregation, simplifies DAX, and enhances scalability.

Key recommendations:
1.Keep semi-additive measures in a separate fact table. This simplifies DAX and prevents incorrect aggregations.
2.Use the periodic snapshot pattern for forecasting. This approach is appropriate for on demand forecasting and monthly balance reporting.

3.Separate tables for performance and scalability. A single consolidated table containing mixed measure types can increase query complexity and impact performance. Separate tables allow partitioning and better model optimisation.

Additionally, please refer to the links below for further information:
Modeling Fact Tables in Warehouse - Microsoft Fabric | Microsoft Learn
Define Semiadditive Behavior | Microsoft Learn
Optimization guide for Power BI - Power BI | Microsoft Learn

We hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.