Latest Transaction at an invoice level - including date filters

[Anonymous]

Im trying to write a RANKX expression to capture the ranking of transactions at a customer invoice level. But taking into account the date / calendar filters I have in place on the report (date slicer).

All transactions have a unique index called transaction id and this is an incremental field, the the larger the number the more recent the transaction naturally.

At the moment the calculated column I am using is:

Latest Transaction = RANKX(FILTER('Finance Transactions',

'Finance Transactions'[Invoice Code] = EARLIEST('Finance Transactions'[Invoice Code])

),'Finance Transactions'[Transaction Id],,DESC)

On my report when I filter to transactions between the 10/07 and 12/07 the rank of transaction on the 14/07 is still seen as the latest (rank 1), while a transaction on the 12/07 should be the latest for that invoice code. How do I get date filters to be taken into consideration?

[Ashish_Mathur]

Hi,

If you are writing that formula as a calculated column formula (as you hav mentioned), then a selection/change in a slicer will not have any effect on a calculated column formula unless you hit refresh.  Without refreshing, a selection/change in a slicer will only effect measures.

[Anonymous]

So I need to use a measure? how can I write this to work as a measure then? I believe the context doesnt work for EARLIER/EALIEST in a measure? Any help would be appreciated.

[Ashish_Mathur]

Yes, you need to write a measure.  Share some data (in a format that can be pasted in an MS Excel file), describe the question and show the expected result.

[Anonymous]

Transaction ID	Transaction Date	Transaction Type	Invoice Code	Amount	Balance
1	10/07/2021	Add	INV0001	10	10
2	12/07/2021	Add	INV0002	20	20
3	12/07/2021	Payment	INV0002	10	10
4	14/07/2021	Credit Note	INV0002	5	5

Example, INV0002 id expext to be rank number 1 if the date selected on the report is for transactions between 10/07 and 12/07 (date slicer). Does that help give context?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Very close - how does it take the context of each INVOCIE Code? e.g. transaction id 1 would also be ranked 1 as its the only transaction for INV00001?

[Ashish_Mathur]

Hi,

Try this measure

Rank1 = if(ISBLANK([Amt]),BLANK(),RANKX(GENERATE(all(Data[Transaction ID]),all(Data[Transaction Type])),[value of transaction id]))

[Anonymous]

Actually its not quite right - 

moving the date to the 14th you can see INV0002 transaction ID 3 should have a rank 2 (under the measure Rank1. And transaction id 2 should be now ranked 3rd. Any ideas?

[Ashish_Mathur]

Hi,

Try this measure

Rank1 = if(ISBLANK([Amt]),BLANK(),RANKX(GENERATE(all('Calendar'[Date]),GENERATE(all(Data[Transaction ID]),all(Data[Transaction Type]))),[value of transaction id]))

Hope this helps.

[Anonymous · Accepted solution]

You got me on the right path though - I think this is working now with the ALLSELECTED 'calendar[Date] instead: 

Rank1 =if(ISBLANK([Amt]),BLANK(),RANKX(GENERATE(ALLSELECTED('Calendar'[Date]),GENERATE(all(Data[Transaction ID]),all(Data[Transaction Type]))),[value of transaction id]))

Just trying it on a larger data set now to see how it behaves

[Anonymous]

Still not quite right as if you slide the date back to the 13th or the 12th ransaction id 3 should be ranked number 1 at this date:

[Anonymous]

Perfect - just trying to apply it to my orginal file now. Can I ask why you used this bit? trying to get my head around why this works. How does it know to rank them at an invoice code level?

all(Data[Transaction Type])

[Ashish_Mathur]

It is ranking all unique combinations of transaction id and transaction type within that invoice code.

If my previous reply helped, please mark it as Answer.

[Anonymous]

Sorry I spoke to soon - did you see my other reply? I only want to rank at the invoice code level, does my last post make sense?