Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 () )
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 () )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |