Matrix to calculate difference from previous record both gap and percentage

Hi stanwang ,

You can create a custom column as below:-

Delta =
VAR _prev =
    CALCULATE (
        SUM ( 'Table (4)'[Revenue] ),
        FILTER (
            ( 'Table (4)' ),
            'Table (4)'[RptDate] < EARLIER ( [RptDate] )
                && 'Table (4)'[Product] = EARLIER ( 'Table (4)'[Product] )
        )
    )
RETURN
    IF ( _prev <> BLANK (), [Revenue] - _prev )

and use this column in Matrix as below:-

Hi stanwang 

1. Dynamic Solution:

My solution stands out as the only dynamic approach among those presented here. Adding a column renders other solutions static, leading to unexpected and inaccurate results when filters or slicers are applied. My solution utilizes a measure, ensuring its dynamic nature and seamless operation even when interactions are added to the report. Please note that you have marked an incorrect solution as the correct one. It displays an erroneous result. (I have highlighted the incorrect solution you marked in red in the attached image.)

More information about the differences between the measure and calculated column is here :

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

2. Total Row Error:

I acknowledge my oversight regarding the total row. It's essential to remember that unlike Excel, the total row in Power BI doesn't simply sum the values above it within the visualization. Instead, it performs the same calculation applied to each category but without their context. Therefore, errors can arise, as happened in my case, if proper analysis is not conducted.
I corrected the measure of the previous value :

PreviousVisibleRevenue =

VAR CurrentProduct = MAX('Table'[Product])

VAR CurrentDate = MAX('Table'[RptDate])

VAR PreviousDate =

    CALCULATE(

        MAX('Table'[RptDate]),

        FILTER(

            ALLSELECTED('Table'),

            'Table'[Product] = CurrentProduct && 'Table'[RptDate] < CurrentDate

        )

    )

RETURN

  if(HASONEVALUE('Table'[Product]),

    CALCULATE(

      [Revenue_],

        FILTER(

           ALLSELECTED('Table'),

            'Table'[Product] = CurrentProduct && 'Table'[RptDate] = PreviousDate

        )

    ),

    CALCULATE(

      [Revenue_],

        FILTER(

           ALLSELECTED('Table'),

            'Table'[RptDate] = PreviousDate

        )))

So now the results are correct including the total  (sorry for the mistake 🙂 )

 

 More information about fixing totals is here:

https://www.sqlbi.com/articles/why-power-bi-totals-might-seem-inaccurate/

The updated PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Hi,

PBI file attached.

Hope this helps.