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

[Mahhin_Shahzad1]

Hi Everyone , 

I'm redesigning our data warehouse for dashboard-based analytics at a SaaS forecasting company, and I need validation on my approach for handling semi-additive measures and overall approach till now ( heads up this will get a little lenthy ) 

TL;DR: Should I create a separate table for Balance Sheet items (semi-additive) or keep them with P&L items (fully additive) in one fact table? I've provided detailed context below to get comprehensive feedback on my overall approach.

## Background

Our company previously had multiple tables designed for paginated financial reports. I've taken the initiative to consolidate these into a single dimensional model optimized for Power BI dashboards, pulling data from various financial statement tables into one fact table at a monthly grain. We have a separate .NET-based engine that handles the forecasting calculations.

## The  New Dashboard Requirements

Recently, I was asked to create dashboards displaying financial ratios across multiple categories. Initially, I was told I'd receive pre-calculated ratios to display. However, I pushed back and convinced stakeholders we need to store the raw base components (numerators and denominators) to enable drill-down, filtering, and proper aggregation across company hierarchies.

## My Current Data Model

I've consolidated everything into a single fact table with monthly grain:

FactMonthlyFinancials

• DateKey
• CompanyKey
• MeasureKey (links to DimMeasure)
• ActualValue
• ForecastValue

This is a kind of a periodic snapshot fact table—when our .NET engine runs a forecast, it populates this table with values for all measures across all relevant months.

The challenge: I'm storing both fully additive measures (P&L items) and semi-additive measures (Balance Sheet items) in the same table.

## How I'm Handling Aggregation in DAX

I've created a flag in my DimMeasure dimension table indicating whether each measure is fully additive or semi-additive. My DAX measures then apply different logic:

• Fully additive measures (Sales, COGS, Expenses): Using DATESYTD() to aggregate across time periods
• Semi-additive measures (Assets, Liabilities, Inventory): Using LASTDATE() to show period-end balances

This works functionally for now , but I'm questioning whether it's the right architectural approach.

## What's Making Me Doubt This Approach

I've been trying to follow Kimball's dimensional modeling practices. I found the DAX Patterns guide on semi-additive calculations (https://www.daxpatterns.com/semi-additive-calculations/), and they use a separate Balance table specifically for semi-additive measures.

## My Questions

1. Should semi-additive measures be in a separate fact table? Or is it acceptable to mix fully additive and semi-additive measures in one table and handle differences in DAX?
2. Is my periodic snapshot pattern appropriate for this on-demand forecasting scenario?
3. Are there performance or scalability concerns with this consolidated approach?
4. What resources or experts can help validate this design? I've mainly used ChatGPT and similar resources to discuss this so far So I Reallywant to get some confidence in my approach .

I want to ensure I'm building a robust, scalable foundation that follows industry best practices and makes it easier to build DAX measures from this single source going forward. Any feedback from experienced professionals is really appreciated!

[v-pnaroju-msft]

Hi Mahhin_Shahzad1,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi Mahhin_Shahzad1,

We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

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.

[TomMartens]

Hey @Mahhin_Shahzad1 ,

according to Kimball's "The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling" there are different types of fact tables, e.g., transaction-based fact tables and snapshot tables.
I consider P&L data as transactional data, meaning two rows can be aggregated over time, e, g., revenues can be summed. In contrast most often balance data must be considered as snapshot data, meaning March data can not be summed with April data, instead it's something like LASTDATE ...
Assuming there is only balance data available until April, then 1. Halfyear is April, 2. Quarter is April, but 1. Quarter is March, and not the sum of Jan - March.

For this reason, I separate "Semi-Additive" measures from the transactional data. I separate the Balance data from the P&L data.

Not sure, if my approach can be considered a best-practice, but until now I did not face issues regarding scalability.

Hopefully, this helps somehow.

Regards,
Tom