Compare with previous row in a visual and show the difference between current and previous row value

[LD2022]

I have a table that has columns like :

DATE        ACCOUNT_BALANCE             ACCOUNT_NAME   BRANCH  MANAGER

01/02         50                                          ABC                       B1             B1M

01/02         90                                          XYZ                       B2             B2M

01/02         100                                          APC                       B3             B3M

02/02         20                                          ABC                       B1             B1M

02/02         10                                          XYZ                       B2             B2M

02/02         100                                          APC                       B3             B3M

I have a visual that shows the top 5 account balances for the last date by Account Short Name:

Account Short Name	Balance	Movements
APC	100	100
ABC	20	-80
XYZ	10	-10

I tried using Index, Calculated columns using earlier functions but nothing seems to work, kindly advise.

Can you please help me how to achieve this in Power BI?

[LD2022]

Hi @v-rzhou-msft ,

Worked like a charm 🙂

I amn't quite clear how it the below fetches the Account Balance value for previous Account Name.

It would be greatly appreciated if you could explain the below:

VAR _Previous_Balance =
    CALCULATE (
        MIN ( 'Table'[ACCOUNT_BALANCE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[ACCOUNT_BALANCE] > _MAX_BALANCE
        )
    )

[v-rzhou-msft]

Hi @LD2022 ,

I suggest you to try to create measures as below to calculate current balance and the difference.

Current Balance = 
CALCULATE (
    SUM ( 'Table'[ACCOUNT_BALANCE] ),
    FILTER ( 'Table', 'Table'[Date] = MAX ( 'Table'[Date] ) )
)

Differences Between Previous and Current Balance = 
VAR _Previous_Balance =
    CALCULATE (
        SUM ( 'Table'[ACCOUNT_BALANCE] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ACCOUNT_NAME] ),
            'Table'[Date] < MAX ( 'Table'[Date] )
        )
    )
RETURN
    [Current Balance] - _Previous_Balance

Result is as below.

Best Regards,
Rico Zhou

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

[LD2022]

Hi @v-rzhou-msft , thanks for your response but that didn't work.

As mentioned earlier, I need the top 5 Account Names to be sorted by Account Balance Desc and show the Current -Previos row variance for the top 5 rows i.e 

APC	100	100
ABC	20	-80
XYZ	10	-10

[v-rzhou-msft]

Hi @LD2022 ,

I think [Balance] should be the latest Balance. Does [Movement] show the difference between current and previous?

For APC I think it should be 100-100.  I am confused about the calculate logic about [Movement]. How can we get it ?

Account Short Name	Balance	Movements
APC	100	100
ABC	20	-80
XYZ	10	-10

Best Regards,
Rico Zhou

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

[LD2022]

@v-rzhou-msft Movement is current-previous row . For APC, there is no previous row so, it is 100-0=100.

[v-rzhou-msft]

Hi @LD2022 ,

For APC, in my understanding, I think 02/02 should be current so current accoun balance is 100. 01/02 should be previous (01/02 is before than 02/02 ) so previous accoun balance is 100. So my result is 100 - 100 = 0.

How can we determind the previuos row? Please show me more details about your calculate logic.

Best Regards,
Rico Zhou

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

[LD2022]

Hi @v-rzhou-msft , the requirement is not to compare with the previous date but with the previous row value in the table.

1. Sort the Account Balance in descending order by Account Short Name for the latest date

2. Find the difference of current and previous row in the table visual i.e.

Account Balance for Current Account Short Name - Account Balance for Previous Account Short name in the table visual.

[v-rzhou-msft · Accepted solution]

Hi @LD2022 ,

I think you can try this code to create a measure.

Differences Between Previous Row = 
VAR _MAX_BALANCE =
    CALCULATE (
        SUM ( 'Table'[ACCOUNT_BALANCE] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ACCOUNT_NAME] ),
            'Table'[Date] = MAX ( 'Table'[Date] )
        )
    )
VAR _Previous_Balance =
    CALCULATE (
        MIN ( 'Table'[ACCOUNT_BALANCE] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[ACCOUNT_BALANCE] > _MAX_BALANCE
        )
    )
RETURN
  _MAX_BALANCE - _Previous_Balance

Result is as below.

Best Regards,
Rico Zhou

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