finding transaction based on latest date - help

[indhu]

Hi all,

I have a table as follows 

Type              userid       date            amount        balance

---------------------------------------------------

deposit          01         01-01-2018      20                101

withdraw       01          02-01-2018     101               00

deposit          02         02-01-2018      05                 05

deposit         02          02-01-2018      10                 15

I want to find the latest balance based on the date for each user. Can somebody please help?

Thanks,

Indhu.

[Anonymous · Accepted solution]

Try these 3 measures:

Deposits = CALCULATE(
	SUM('YourTable'[amount]),
	ALL('YourTable'),
	'YourTable'[Type] = "deposit"
)

Withdrawals = CALCULATE(
	SUM('YourTable'[amount]),
	ALL('YourTable'),
	'YourTable'[Type] = "withdraw"
)

Balance = [Deposits] - [Withdrawals]

[indhu]

@Anonymous Thanks for your reply. Sometimes the record doesn't match. I want to find the latest date in spite of the transaction type I want to find the latest date, and get the balance alone. Is that possible?

Thanks,

Indhu 

[Anonymous]

LASTDATE('Table'[DateField])

That formula will provide you with the latest date in a table.  You could put that inside a calculate statement.

[indhu]

This gets the latest date on the whole 😞 not filtering based on the user id. I.e. individually.

when I used with condition, 

latestdate = CALCULATE(LASTDATE(table[date]),ALLEXCEPT(table,table[userid]))

still failed as the date column duplicates 😞 

Any help is much appreciated @Anonymous.

Thanks,

Indhu

[Anonymous]

In that case, switch LASTDATE to MAX