Forum Discussion

hildasteyn's avatar
hildasteyn
Icon for Helper I rankHelper I
2 years ago
Solved

Extracting the latest date from a table

I have a table with investments - the PK is investmentnumber.

I have a table with month end balances - it has a lookup to the investment table, a date, and an amount. 

I am currently building a report and I want to extract the latest balance from the month end balance table. 

I assume I will have to use a measure, but I cannot get the dax to work. 

Please assist. 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi hildasteyn 

     

    Here is a similar topic for your reference: How to get value from last date (most recent) by category using a measure

    You can try the solution provided there.

     

    If the solution doesn't work, please provide some dummy data and expected result that we can work with as PhilipTreacy has suggested. Thanks. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

    • hildasteyn's avatar
      hildasteyn
      Icon for Helper I rankHelper I
      2 years ago

      Hi Jing

      Thank you for your reply, but I am still struggling.

      Tablename: tablebalance

      Column names: date, amount

       

      I created a measure LastBalanceDate: 

      LastBalanceDate = MAX(tablebalance[Date])
      That works fine - it shows the most recent date.
       
      Now I need another measure to extract the amount for the most recent date.
      I tried this, but the wheels just keep on turning and everything hangs:
       
      LastBalanceAmount =
      VAR LastBalanceDate = [LastBalanceDate]
      RETURN
          CALCULATE(
              SELECTEDVALUE(tablebalance[Amount]),
              tablebalance[Date] = LastBalanceDate
          )
       
      What am I doing wrong?
    • hildasteyn's avatar
      hildasteyn
      Icon for Helper I rankHelper I
      2 years ago

      I tried this too (taken from the link you gave me):

      LastBalanceAmount =
      VAR LastBalanceDate=CALCULATE(MAX(tablebalance[Date]),
      FILTER(ALL(tablebalance),[Date]=[LastBalanceDate]))
      RETURN
      CALCULATE(MAX(tablebalance[Amount]),FILTER(ALL(tablebalance),[Date]=[LastBalanceDate]))