Taking the account balance from last transaction number

[Anonymous]

Hi, 

I'm trying to run a report where it shows a customers balance from the transactions that were made on a certain date. 

However, I'm trying to show only the balance from the latest transaction record as opposed to all of the balances showing in the table (see picture for number 01963). I also have a slicer on this table to show for each certain date so the table content will change and I need this to change with it. 

Any help is much appreciated!

Josh

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Here I created two measures to work on it, please check whether it is that your excepted result.

Measure = 
VAR cal =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Transaction Number] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[Tebabcy No] )
    )
var a  =
    IF(cal>1,1,BLANK())
return
a

Measure 2 = 
VAR d =
    MAXX ( FILTER ( ALLSELECTED ( 'Table' ), [Measure] = 1 ), 'Table'[date] )
VAR ty =
    CALCULATE (
        MAX ( 'Table'[Tebabcy No] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] = d )
    )
RETURN
    IF ( MAX ( 'Table'[Tebabcy No] ) = ty, 1, BLANK () )

[Anonymous]

hi @v-frfei-msft, 

Many thanks for your reply. 

However, its not quitte what I'm after. 

I have some tenancies that have had more than one transaction in a week and I only want to display the latest one (sorted by transaction number) and display the balance alongside it. 

e.g:

Tenancy	Transaction date	Transaction Number	Transaction value	Current Balance
01987	23.11.19	11	-£20	£10
01987	23.11.19	10	-£10	£30
01987	22.11.19	6	£100	£40
01987	22.11.19	5	-£20	-£60

In this case I would only like transaction number 11 to be shown on my visual if I selected 23.11.19 on my slicer, likewise transaction 6 to be shown if I selected 22.11.19 on my slicer. 

I am also using two different tables for this visual: tenancies and transactions. 

Tenancies contains tenancy no and transactions contain transaction number, date and value. 

I hope this is clear to you and thanks again for your help!!

Josh