Forum Discussion

Sachy123's avatar
Sachy123
Helper V
6 years ago
Solved

Get latest Balance

I have a COA table which comes as a result of direct query in the following format. DateID COA Balance 1 1001 50 1 1002 60 2 1003 100 2 1001 30 3 1002 80 4 1003 60...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try like

    LASTNONBLANKVALUE(Table[DateID], max(Table[Balance]))
    Or
    LASTNONBLANKVALUE(Table[DateID], sum(Table[Balance]))

     

    Date Join should be on date ID. You can try using Date[Date] in place of Table[Date ID]

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Try like

LASTNONBLANKVALUE(Table[DateID], max(Table[Balance]))
Or
LASTNONBLANKVALUE(Table[DateID], sum(Table[Balance]))

 

Date Join should be on date ID. You can try using Date[Date] in place of Table[Date ID]

 

Sachy123's avatar
Sachy123
Helper V
6 years ago
still trying to figure out.. but how and where can i add the relationship?
  • Sachy123's avatar
    Sachy123
    Helper V
    6 years ago
     

     

     

     

    • Sachy123's avatar
      Sachy123
      Helper V
      6 years ago

      does someone know what could be the solution? .. any hint would do!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Sachy123 ，

        You can create a measure as below:

        Latest Balance = 
VAR ccoa =
    MAX ( 'COA'[COA] )
RETURN
    SUMX (
        VALUES ( 'COA'[COA] ),
        CALCULATE (
            MAX ( 'COA'[Balance] ),
            FILTER ( 'COA', 'COA'[COA] = ccoa && 'COA'[DateID] = MAX ( 'COA'[DateID] ) )
        )
    )

        Best Regards

        Rena