Forum Discussion

jwin2424's avatar
jwin2424
Icon for Resolver I rankResolver I
4 years ago
Solved

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

  • smpa01's avatar
    smpa01
    4 years ago

    jwin2424  please use the following

     

    _Balance = 
CALCULATE (
    MAX ( 'Table'[Balance] ),
    FILTER (
        'Table',
        VAR _mxIndex =
            CALCULATE ( MIN ( 'Table'[Index] ), ALL ( 'Table' ) )
        RETURN
            'Table'[Index] = _mxIndex
    )
)

     

     

     

     

8 Replies

    • jwin2424's avatar
      jwin2424
      Icon for Resolver I rankResolver I
      4 years ago

      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!

    • jwin2424's avatar
      jwin2424
      Icon for Resolver I rankResolver I
      4 years ago

      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. 

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

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

         

         

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

    • jwin2424's avatar
      jwin2424
      Icon for Resolver I rankResolver I
      4 years ago

      This worked as well. Thank you!