Forum Discussion
Balance from latest transaction number
Hi All,
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
10 Replies
- JarroVGIT
Resident Rockstar
You can use a RANK() calculated column and filter on that. However, you are mentioning two tables. Are those tables related?
Assuming that your transaction table is similar to the table you gave in your post, the calculated column would be something like this:
RankedPerDayPerTenancy = VAR currentTenancy = Table3[Tenancy] VAR currentDate = Table3[Transaction date] RETURN RANKX( FILTER(Table3, Table3[Tenancy] = currentTenancy && Table3[Transaction date] = currentDate), Table3[Transaction Number],,DESC,Dense)This makes the table look like this:
Now if you have a slicer on the Transaction Date column, and you put a VIsual Filter on the newly calculated column where the value = 1, you get the result you desire 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Hi JarroVGIT
Thanks for your reply. However when I do this every transaction is returning the number 1 so doesn't really help (I might be doing something wrong but not sure).
I also would rather now use the week number instead of a date field as the filter which i think would make life easier?
v-lid-msftCommunity Support
Hi Anonymous ,
We can create a measure and use it in visual filter to meet your requirement:
IsLastNumber = IF(SELECTEDVALUE('Table'[Transaction Number]) = CALCULATE(MAX('Table'[Transaction Number]),ALLSELECTED('Table')),1, -1)
Best regards,
- v-lid-msft
Hi Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,- v-lid-msft
Hi Anonymous ,
We can use the following measure to meet your requirement:
IsLastNumber = IF(CALCULATE(MAX('Table'[Transaction Number]),FILTER(ALLSELECTED('Table'),'Table'[Tenancy] in FILTERS('Table'[Tenancy]) && 'Table'[Transaction date] in FILTERS('Table'[Transaction date]))) in FILTERS('Table'[Transaction Number]),1, -1)
Best regards,
