Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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 ) )
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.
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 |
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.
@v-rzhou-msft Movement is current-previous row . For APC, there is no previous row so, it is 100-0=100.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
3 | |
1 | |
1 | |
1 |