Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Latest Transaction at an invoice level - including date filters

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?
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Transaction IDTransaction DateTransaction TypeInvoice CodeAmountBalance
110/07/2021AddINV00011010
212/07/2021AddINV00022020
312/07/2021PaymentINV00021010
414/07/2021Credit NoteINV000255

 

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?

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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?

Hi,

Try this measure

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Actually its not quite right - 

lgarton_0-1626751878515.png

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?

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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
Not applicable

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:

lgarton_0-1626817682021.png

 

Anonymous
Not applicable

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])

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.