Difference of each sub-category in matrix visual

[navafolk]

Hi all,

I have the data 'Table'

The basic matrix visual looks like (row goes with 2 level categories: Version_date -> Product)

Please help to create measure adding rows for difference between 2 latest Version_date. The difference will be for 2 levels of row: Version_date, Product. My expected would be:

Thank you, all.

I did try some var and return measure but it can give difference for Version_date level only.

Diff = 
var _max=CALCULATE(MAX('Table'[Version_date]),ALLSELECTED('Table'))
var _min=CALCULATE(MAX('Table'[Version_date]),FILTER(ALLSELECTED('Table'),[Version_date]<_max))
return 
IF(ISINSCOPE('Table'[Version_date]),
   sum('Table'[Amount]),
   CALCULATE(sum('Table'[Amount]),FILTER('Table','Table'[Version_date]=_max))-CALCULATE(sum('Table'[Amount]),FILTER('Table','Table'[Version_date]=_min)))

[Elena_Kalina]

Hi @navafolk 

Based on your requirements, you need a measure that calculates differences between the two latest version dates at both the Version_date level and Product level. Try this solution:

Amount Diff = 
VAR CurrentVersion = SELECTEDVALUE('Table'[Version_date])
VAR CurrentProduct = SELECTEDVALUE('Table'[Product])

// Get the two most recent version dates in context
VAR AllVersions = CALCULATETABLE(VALUES('Table'[Version_date]), ALLSELECTED('Table'))
VAR MaxVersion = MAXX(AllVersions, [Version_date])
VAR PreviousVersion = MAXX(FILTER(AllVersions, [Version_date] < MaxVersion), [Version_date])

// Calculate amounts for current context
VAR CurrentAmount = SUM('Table'[Amount])

// Calculate amounts for max and previous versions
VAR MaxVersionAmount = 
    CALCULATE(
        SUM('Table'[Amount]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Version_date] = MaxVersion &&
            (ISBLANK(CurrentProduct) || 'Table'[Product] = CurrentProduct)
    )

VAR PreviousVersionAmount = 
    CALCULATE(
        SUM('Table'[Amount]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Version_date] = PreviousVersion &&
            (ISBLANK(CurrentProduct) || 'Table'[Product] = CurrentProduct)
    )

// Determine what to return based on context
RETURN
    IF(
        ISINSCOPE('Table'[Version_date]),
        // If at Version_date level, show regular amount or difference
        IF(
            COUNTROWS(AllVersions) >= 2 && CurrentVersion = MaxVersion,
            CurrentAmount - PreviousVersionAmount,
            CurrentAmount
        ),
        // If at Product level or total, show difference
        IF(
            COUNTROWS(AllVersions) >= 2,
            MaxVersionAmount - PreviousVersionAmount,
            BLANK()
        )
    )

[navafolk]

Thank you, @Elena_Kalina.

I have tried, but it was quite weird. Please help with this.

[Elena_Kalina]

I've been trying different ways to get this matrix looking exactly how you want, but I'm hitting a snag. No matter what I try, the "Total Difference" keeps showing up multiple times in the subtotals, which leaves weird empty spaces and makes the whole thing look messy.

The closest I've gotten is making a separate matrix with just those three key rows you need (like in your example screenshot). Not perfect, but as a quick fix, we could maybe just stick this simplified version next to your original table.

[Elena_Kalina]

Total Amount = SUM('Table'[Amount])

Product Difference =
VAR CurrentProduct = SELECTEDVALUE('Table'[Product])  
VAR Sum07Jun = CALCULATE(  
    [Total Amount],  
    'Table'[Version_date] = DATE(2025,6,7),  
    'Table'[Product] = CurrentProduct  
)  
VAR Sum01Jun = CALCULATE(  
    [Total Amount],  
    'Table'[Version_date] = DATE(2025,6,1),  
    'Table'[Product] = CurrentProduct  
)  

// For the "Total" row  
VAR TotalSum07Jun = CALCULATE(  
    [Total Amount],  
    'Table'[Version_date] = DATE(2025,6,7)  
)  
VAR TotalSum01Jun = CALCULATE(  
    [Total Amount],  
    'Table'[Version_date] = DATE(2025,6,1)  
)  

RETURN  
IF(  
    ISINSCOPE('Table'[Product]),  
    // Difference for products A/B  
    Sum07Jun - Sum01Jun,  
    // Difference for the "Total" row  
    TotalSum07Jun - TotalSum01Jun  
)  

 Perhaps you will be useful to the formulas that I used for this