cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwin2424
Resolver I
Resolver I

Grab data from most recent row available

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?

 

 ABCD
 Posting DateDescriptionAmountBalance
112/5/2021Groceries-53.62674.45
212/5/2021Gas-42.81728.07
312/5/2021Rent-1800770.88
412/5/2021Utilities-213.122570.88

 

 

Thanks!

 

Joe

1 ACCEPTED 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
    )
)

 

 

smpa01_0-1639066498137.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

8 REPLIES 8
v-robertq-msft
Community Support
Community Support

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:

vrobertqmsft_0-1639019084966.png

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:

vrobertqmsft_1-1639019084969.png

 

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!

amitchandak
Super User
Super User

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

 

smpa01_0-1638909756437.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


jwin2424_0-1638911874212.png


 ABCDE
 Posting DateDescriptionAmountBalanceIndex
112/5/2021Groceries-53.62674.451
212/5/2021Gas-42.81728.072
312/5/2021Rent-1800770.883
412/5/2021Utilities-213.122570.884


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

 

 

smpa01_0-1639066498137.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors