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)))

[v-veshwara-msft]

Hi @navafolk ,

Thanks for posting in mIcrosoft Fabric Community and sharing the detailed requirement.

To get the matrix exactly as you described, including row under the version and product breakdown - I used a calculated table. 

The calculated table (DeltaTable) compares the two most recent Version_date values and creates rows that represent the difference in Amount for each product and month. I then combined this with the original table.

Here’s the DAX for the calculated table(DeltaTable):

DeltaTable = 
VAR AllVersions = VALUES(Data[Version_date])
VAR MaxVersion = CALCULATE(MAX(Data[Version_date]), ALL(Data))
VAR PrevVersion = CALCULATE(
    MAX(Data[Version_date]),
    FILTER(ALL(Data[Version_date]), Data[Version_date] < MaxVersion)
)
VAR V1 = FILTER(Data, Data[Version_date] = PrevVersion)
VAR V2 = FILTER(Data, Data[Version_date] = MaxVersion)

RETURN
SELECTCOLUMNS(
    ADDCOLUMNS(
        GENERATE(
            SUMMARIZE(V2, Data[Product], Data[Date]),
            VAR p = [Product]
            VAR d = [Date]
            VAR amtV1 = CALCULATE(SUM(Data[Amount]), V1, Data[Product] = p, Data[Date] = d)
            VAR amtV2 = CALCULATE(SUM(Data[Amount]), V2, Data[Product] = p, Data[Date] = d)
            RETURN ROW("Product1", p, "Date1", d, "DeltaAmount", amtV2 - amtV1)
        ),
        "Month", FORMAT([Date], "MMMM")
    ),
    "Version", "Total",
    "Product", [Product],
    "Month", [Month],
    "Amount", [DeltaAmount]
)

Then combined this with the original table using:

CombinedTable = 
UNION (
    SELECTCOLUMNS(
        Data,
        "Version", FORMAT([Version_date], "dd-MM-yyyy"),
        "Product", [Product],
        "Month", FORMAT([Date], "MMMM"),
        "Amount", [Amount]
    ),
    DeltaTable
)

Used this CombinedTable in the matrix visual by placing:

Version and Product in Rows

Month in Columns

TotalAmount1 in Values

This setup gave the exact structure you showed in your example. The rows update automatically based on the latest two version dates in your data.

Hope this helps. Please reach out for further assistance.

Please consider marking the helpful reply as Accepted Solution to assist others with similar issues.

please find attached .pbix for reference.
Also, thanks to @Elena_Kalina for response and approach.

[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