Showing the latest value

[Serdet]

Hi All,

I have a table that looks like the following;

Date	Total Amount Owed
01/01/2020	£1,000.00
01/02/2020	£2,000.00
01/03/2020	£8,000.00
01/04/2020	£6,000.00
01/05/2020	£8,000.00

I am creating a report that shows the current total amount owed amount. How can I show this because when I drop the 'Total Amount Owed' column into a card it adds all of these values together instead of showing me the latest £8,000 figure.

Cheers, 

E

[amitchandak · Accepted solution]

@Serdet , try lastnonblankvalue

lastnonblankvalue(Table[Date], max(Table[Total Amount Owed]))

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

[Cbutler]

Hi @amitchandak 

How do you pull back the actual date that the latest value was made on? 
I only want to show the latest value, if I "dont summerise" the date it brings back all the values, If I say Min will bring the min date on all the table values (01/04/2020) and if MAX the max date on all values ("Today")? 
Any ideas?

eg Table: 

Provider	Comment	Date
A	Comment 1	01/07/2020
A		03/07/2020
A	Comment 2	22/07/2020
A		31/07/2020
B	Comment 1	01/05/2020
B	Comment 2	01/06/2020
B		28/06/2020
B	Comment 3	12/07/2020
B		22/07/2020

Pulls back 

Provider	Comment	Date
A	Comment 2	22/07/2020
B	Comment 3	12/07/2020

[az38]

Hi @Serdet 

try to create a measure and put it into card visual

Measure = 
var _lastDate = CALCULATE(MAX(Table[Date]))
RETURN
CALCULATE(MAX(Table[Total Amount]), Table[Date] = _lastDate )

[Serdet]

Hi,

I have entered the following and have received an error message (the sytax is incorrect)

Current WIP =

var _lastDate = CALCULATE(MAX('Cape Coms'[Date]))

RETURN

CALCULATE(MAX('Cape Coms'[Total WIP (£)]), 'Cape Coms'[Date] = LASTDATE)

Please note the above-mentioned columns are my actual column names.

Please can you help?

[az38]

@Serdet 

of course it is incorrect by syntax, please, pay attention closely to my statement.

replace your LASTDATE to variable name _lastDate

[mahoneypat]

You can use a measure like this to get the latest value

Latest Amt Owed =
VAR latestdate =
MAX ( Table[Date] )
RETURN
CALCULATE ( MAX ( Table[Total Amount Owed] ), Table[Date] = latestdate )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat