Rotating Measures in DAX: When your data won't stay put

TL;DR

• Products that change category over time → “rotating measures” problem
• The client wants to see everything reclassified according to the latest period
• Pattern: capture the last period’s classification and apply it backwards
• Use REMOVEFILTERS + temp table with current classification

---

The mess

Let me paint you a scenario I’ve hit more than once: you have products (or customers, or whatever) that change categories over time. A product that was “Premium” in Q1 gets downgraded to “Standard” in Q2 because it didn’t sell as expected.

The user opens Power BI, selects Q1 and Q2 in the filter, and asks:

How much did we sell in Premium?

And here comes the awkward moment: do you sum what WAS Premium in Q1, or what IS Premium according to the current classification?

90% of the time, the client wants the latter. They want to see everything reclassified according to the latest period. But DAX, bless its heart, by default sums everything according to how it was classified at the time.

The solution: Rotating Measures

The pattern has a trick, but once you get it, it’s a piece of cake:

1. Get the last period in the filter context
2. Look up how products are classified IN THAT period
3. Apply that classification backwards to ALL periods

Rotating Sales =
VAR LastPeriod =
    MAXX(
        VALUES(Calendar[Period]),
        Calendar[Period]
    )
VAR ClassifiedProducts =
    CALCULATETABLE(
        SUMMARIZE(
            Products,
            Products[ID],
            Products[Category]
        ),
        Products[Period] = LastPeriod
    )
RETURN
CALCULATE(
    [Sales],
    REMOVEFILTERS(Products[Category]),
    ClassifiedProducts
)

Why it works

• MAXX + VALUES: Gets the most recent period from what the user selected
• CALCULATETABLE + SUMMARIZE: Creates a temporary table with the classification snapshot from that period
• REMOVEFILTERS: Clears the original category filters (otherwise they’d clash)
• ClassifiedProducts: Reapplies the last period’s classification to the entire query

Basically you’re saying: “ignore how things were classified before, use ONLY the current classification.”

Bonus: showing the current category as text

If you also want a column that shows the current category (not summing, just showing text), the trick is SELECTEDVALUE:

Current Category =
VAR LastPeriod =
    MAXX(
        VALUES(Calendar[Period]),
        Calendar[Period]
    )
RETURN
CALCULATE(
    SELECTEDVALUE(Products[Category]),
    Products[Period] = LastPeriod
)

At totals it’ll return BLANK, which is correct. It doesn’t make sense to show a category when multiple products are mixed together.

Where you’ll need this

Any business where things change buckets:

• Retail with products that change season or section
• Services with clients that move up/down tiers
• Sales teams where reps change territory
• Inventory with products that get reclassified

If you have a dimension that isn’t static over time, sooner or later you’ll need this.

New to DAX? Start with my beginner’s guide before diving into these advanced patterns. If you want to understand time intelligence in more depth, my post on rolling 12 months covers another complex temporal pattern that often trips people up.