Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to create a Card visual to show the current balance of my account. In the example below, I would lilke to have the card display the balance from the most recent row in the data (D1). My bank does not display timestamps, so I only have the date to work with. Thankfully, the most recent transaction is always displayed at the end of the data. Unfortunately, I have multiple dates, so I can't grab the "most recent date" without it returning a sum of ALL of 12/5, rather than just the most recent row.
How would I filter the Card visual to show the most recent row's data?
A | B | C | D | |
Posting Date | Description | Amount | Balance | |
1 | 12/5/2021 | Groceries | -53.62 | 674.45 |
2 | 12/5/2021 | Gas | -42.81 | 728.07 |
3 | 12/5/2021 | Rent | -1800 | 770.88 |
4 | 12/5/2021 | Utilities | -213.12 | 2570.88 |
Thanks!
Joe
Solved! Go to Solution.
@jwin2424 please use the following
_Balance =
CALCULATE (
MAX ( 'Table'[Balance] ),
FILTER (
'Table',
VAR _mxIndex =
CALCULATE ( MIN ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
'Table'[Index] = _mxIndex
)
)
Hi,
According to your description and sample data, I can roughly understand your requirement, it seems that you want to get the most recent balance value based on your newly added index column, right? I think you can try this measure:
This is the test data I created based on your description:
Measure =
var _minindex=MINX(ALL('Table'),'Table'[Index])
return
CALCULATE(SUM('Table'[Balance]),FILTER(ALL('Table'),'Table'[Index]=_minindex))
And you can palce a measure into the card chart to get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This worked as well. Thank you!
@jwin2424 , You need to add an index column and try
Lastnonblankvalue(Table[Index], max(Table[Amount] ))
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
Actually the index column option is in there, just under the transform data mode. I added the index, and unless I am missing something, the formula just returns the index column verbatum.
@jwin2424 once you have index, you can put that into use like this
_Balance =
CALCULATE (
MAX ( 'Table'[Balance] ),
FILTER (
'Table',
VAR _mxIndex =
CALCULATE ( MAX ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
'Table'[Index] = _mxIndex
)
)
A | B | C | D | E | |
Posting Date | Description | Amount | Balance | Index | |
1 | 12/5/2021 | Groceries | -53.62 | 674.45 | 1 |
2 | 12/5/2021 | Gas | -42.81 | 728.07 | 2 |
3 | 12/5/2021 | Rent | -1800 | 770.88 | 3 |
4 | 12/5/2021 | Utilities | -213.12 | 2570.88 | 4 |
You'll have to forgive me, as I am still learning how to use Power BI. My table has the index column (created in edit query) and then I went to my visualization section and created a card. I made a measure (by selecting the table I wanted it for in the fields area and then selecting "add measure). I entered in the formula, and it looks like I got a massive balance in return.
The dats is pulled in decending order, so the most recent transaction is at the very top. I simple want the card to say "674.45."
Thank you for helping!
@jwin2424 please use the following
_Balance =
CALCULATE (
MAX ( 'Table'[Balance] ),
FILTER (
'Table',
VAR _mxIndex =
CALCULATE ( MIN ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
'Table'[Index] = _mxIndex
)
)
I am running on Windows 7, and I don't have the "Insert Index Column" function from the newest Power BI. Is there a DAX formula I can enter into the new column that does the same thing?
Thanks!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |