Forum Discussion

indhu's avatar
indhu
Icon for Helper III rankHelper III
8 years ago
Solved

finding transaction based on latest date - help

Hi all,   I have a table as follows    Type              userid       date            amount        balance --------------------------------------------------- deposit          01         01-01...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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]
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
indhu
Icon for Helper III rankHelper III
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago
    LASTDATE('Table'[DateField])

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

    • indhu's avatar
      indhu
      Icon for Helper III rankHelper III
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        In that case, switch LASTDATE to MAX