cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Matrix to calculate difference from previous record both gap and percentage

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.

3 ACCEPTED SOLUTIONS
Community Champion

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

Best Regards,
Samarth

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

Super User

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

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

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

Regards,
Rita Fainshtein | Microsoft MVP
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
11 REPLIES 11
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

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

Regards,
Rita Fainshtein | Microsoft MVP
Regular Visitor

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

Super User

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

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

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

Regards,
Rita Fainshtein | Microsoft MVP
Regular Visitor

@Ritaf1983 Thank you so much. It is perfect.

Super User

Happy to help 🙂

Regards,
Rita Fainshtein | Microsoft MVP
Super User

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

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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Community Champion

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

Best Regards,
Samarth

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

Regular Visitor

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

Below is excel mockup.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.