Forum Discussion
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...
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]
does someone know what could be the solution? .. any hint would do!
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
Anonymous
well,, the coa table is a direct query,,, so basically only pulls the data for that selected date,,, so i tried this measure but it didnt work 😞
Hi Sachy123 ,
When select the DataID as 1, 2 or 3, what will display on the visual? Could you please explain the logic of display value? Thank you.
Best Regards
Rena
Anonymous The expected out put is as below
Latest Balance at DateID 4 COA Balance 1001 30 1002 80 1003 60 Latest Balance at DateID 3 COA Balance 1001 30 1002 80 1003 100 Latest Balance at DateID 2 COA Balance 1001 30 1002 60 1003 100 Latest Balance at DateID 1 COA Balance 1001 50 1002 60 1003 0