This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
   --> Create model calculations by using DAX
      --> Create Semi-Additive Measures

Note that there are 10 practice questions (with answers and explanations) at the end of each topic. Also, there are 2 practice tests with 60 questions each available on the hub below all the exam topics.

What Are Semi-Additive Measures?

A semi-additive measure is a measure that aggregates normally across some dimensions (such as product, customer, or geography) but not across time.

Unlike fully additive measures (such as Sales Amount or Quantity), semi-additive measures require special handling over date-related dimensions because summing them across time produces incorrect or misleading results.

Common real-world examples include:

• Account balances
• Inventory levels
• Headcount
• Snapshot metrics (daily totals, end-of-period values)

On the PL-300 exam, you are expected to:

• Recognize when a metric is semi-additive
• Know why SUM is incorrect in certain time scenarios
• Implement correct DAX patterns using CALCULATE, time intelligence, and iterators

Why Semi-Additive Measures Matter on the Exam

Microsoft tests your ability to:

• Model business logic correctly
• Apply DAX that respects time context
• Avoid common aggregation mistakes

A frequent exam scenario:

“A report shows incorrect totals when viewing monthly or yearly data.”

This is often a semi-additive measure problem.

Common Types of Semi-Additive Behavior

Semi-additive measures usually fall into one of these patterns:

1. Last Value in Time

Used when you want the ending balance of a period.

Examples:

• Bank account balance
• Inventory at end of month

2. First Value in Time

Used for beginning balances.

3. Average Over Time

Used when a snapshot value should be averaged rather than summed.

Examples:

• Average daily headcount
• Average inventory level

Core DAX Patterns for Semi-Additive Measures

1. Last Non-Blank Value Pattern

This is the most common semi-additive pattern on the PL-300 exam.

Ending Balance :=
CALCULATE(
    SUM(FactBalances[BalanceAmount]),
    LASTDATE('Date'[Date])
)

✅ Aggregates correctly across:

• Product
• Customer
• Geography

❌ Does not sum across time
✔ Returns the last value in the selected period

2. LASTNONBLANK Pattern

Used when data is not available for every date.

Ending Balance :=
CALCULATE(
    SUM(FactBalances[BalanceAmount]),
    LASTNONBLANK(
        'Date'[Date],
        SUM(FactBalances[BalanceAmount])
    )
)

Exam Tip:
Expect questions where data has missing dates — this pattern is preferred over LASTDATE.

3. First Value (Beginning Balance)

Beginning Balance :=
CALCULATE(
    SUM(FactBalances[BalanceAmount]),
    FIRSTDATE('Date'[Date])
)

4. Average Over Time Pattern

Instead of summing daily values, average them.

Average Daily Balance :=
AVERAGEX(
    VALUES('Date'[Date]),
    SUM(FactBalances[BalanceAmount])
)

Key Concept:
Use an iterator (AVERAGEX) to control aggregation over time.

Why SUM Is Usually Wrong

Example:

• Inventory = 100 units each day for 30 days
• SUM = 3,000 units ❌
• Correct answer = 100 units (ending) or average (100) ✔

PL-300 Insight:
If the value represents a state, not an activity, it’s likely semi-additive.

Filter Context and CALCULATE

Semi-additive measures rely heavily on:

• CALCULATE
• Date table filtering
• Time intelligence functions

The exam frequently tests:

• Understanding how filter context changes
• Choosing the correct date function

Relationship to Time Intelligence

Semi-additive measures often work alongside:

• LASTDATE
• FIRSTDATE
• DATESMTD, DATESQTD, DATESYTD
• ENDOFMONTH, ENDOFYEAR

Example:

Month-End Balance :=
CALCULATE(
    SUM(FactBalances[BalanceAmount]),
    ENDOFMONTH('Date'[Date])
)

Best Practices (Exam-Relevant)

• Always use a proper Date table
• Avoid calculated columns for semi-additive logic
• Use measures with CALCULATE
• Identify whether the metric represents:
  • A flow (additive)
  • A snapshot (semi-additive)

How This Appears on the PL-300 Exam

Expect:

• Scenario-based questions
• “Why is this total incorrect?”
• “Which DAX expression returns the correct value?”
• Identification of incorrect SUM usage

You may be asked to:

• Choose between SUM, AVERAGEX, and CALCULATE
• Select the correct date function
• Fix a broken measure

Key Takeaways

• Semi-additive measures do not sum correctly over time
• They require custom DAX logic
• CALCULATE + date functions are essential
• Recognizing business meaning is just as important as writing DAX

Practice Questions

Go to the Practice Exam Questions for this topic.