Matrix to calculate difference from previous record both gap and percentage

[stanwang]

I am new to BI. How to get different from previous record both value and percentage.

For example my table as below

Product	Revenue	RptDate
A	100	1/1/2024
A	200	3/7/2024
A	150	7/8/2024
B	300	1/1/2024
B	420	3/7/2024
B	250	7/8/2024
C	300	1/1/2024
C	250	3/7/2024
C	600	7/8/2024

The output visual is as below,

 

I can do it in excel, but how to achieve it in BI. Thanks.

[Samarth_18 · Accepted solution]

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

[Ritaf1983 · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[stanwang]

@Ashish_Mathur It is awesome. This is the best solution, easy to understand and implement. Thank you.

[Ashish_Mathur]

You are welcome.

[Ritaf1983]

Hi @stanwang 

In the first step, I recommend adding an index column by-product, using linked method

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

The result will look like:

Then you can create these measures:

Revenue_ = sum('Table'[Revenue])

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

    CALCULATE(

      [Revenue_],

        FILTER(

            ALL('Table'),

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

        )

    )

delta_ = if(ISBLANK([PreviousVisibleRevenue]),"", [Revenue_]-[PreviousVisibleRevenue])

Result :

pbix is attached

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

[stanwang]

@Ritaf1983 Your method is complicated, but it works fine for me. Just curious about highlighted in red. Why it is not 170?

[Ritaf1983 · Accepted solution]

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

[stanwang]

@Ritaf1983 Thank you so much. It is perfect.

[Ritaf1983]

Happy to help 🙂

[AllisonKennedy]

@stanwang  you could also do this in Power Query using this method: 

Referencing the Next Row in Power Query • My Online Training Hub

[Samarth_18 · Accepted solution]

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

[stanwang]

@Samarth_18Delta from 2nd to 1st is correct, but detal from 3rd to 2nd is not what I want.

Below is excel mockup.