How to use filter based on latest x transactions

[DeBIe]

Hi all,

I am trying to write the logic in PowerBI to return a result based on the latest x(5) transactions from all suppliers. this result should tell me if suppliers have been accounting me the same price or not. I know how to use a date filter for the current month or x previous days. But in this case, I don't know how to filter my measure to check the last 5 transactions. This has to be done for all suppliers (in my dataset I have hundreds).

Here is my example data set. Underneath I will show my expected result:

SupplierName	InvoiceID	SamePrice True/False	PaymentDate
Amazon	A-10	True	31-12-2020
Amazon	AA-10	True	1-1-2021
Amazon	A-23	True	2-1-2021
Amazon	A-11	True	4-1-2021
Amazon	A-13	True	5-1-2021
Amazon	AA-290	True	7-1-2021
Amazon	A-5	True	7-1-2021
Ebay	E-1	True	4-1-2021
Ebay	E-2	False	5-1-2021
Ebay	EE-1	True	6-1-2021
Ebay	E-31	True	8-1-2021
Ebay	E-9	True	12-1-2021
Ebay	E-111	True	13-1-2021
Wallmart	W-4	True	5-1-2021
Wallmart	W-5	True	5-1-2021
Wallmart	W-6	True	6-1-2021
Wallmart	W-9	True	9-1-2021

Expected Result

Suppliers	Total Transactions	SamePriceLast(5)Transactions
Amazon	7	True
Ebay	6	False
Wallmart	4	False (because 5 transactions need to be made)

Thank you kindly for your time.

[v-xulin-mstf · Accepted solution]

Hi @DeBIe,

You can use the following method to meet your need:

Step1: Add an index column:

Step2: Create three measures as:

Total Transactions =

CALCULATE(

    COUNTROWS('Table'),

    FILTER(

        'Table',

        'Table'[SupplierName]='Table'[SupplierName]

    ))

measure 1 =

var _rank=RANKX(

FILTER(

    ALL('Table'),

    'Table'[SamePrice True/False]=TRUE()&& 'Table'[SupplierName]=MAX('Table'[SupplierName])

),

CALCULATE(MAX('Table'[PaymentDate])),,DESC

)

Return

IF(SELECTEDVALUE('Table'[SamePrice True/False])=FALSE(),BLANK(),IF(_rank<=5,1,0)



)

SamePriceLast(5)Transactions =

var _index=CALCULATE(MIN('Table'[Index]),FILTER(ALLSELECTED('Table'),'Table'[measure 1]&&'Table'[SupplierName]=MAX('Table'[SupplierName])))

var _indexmax=CALCULATE(MAX('Table'[Index]),FILTER(ALLSELECTED('Table'),'Table'[measure 1]&&'Table'[SupplierName]=MAX('Table'[SupplierName])))

Return

IF(_indexmax-_index<>4,FALSE(),TRUE())

Step3: Here is the output:

Here is the demo, please try it: How to use filter based on latest x transactions

Best Regards,

Link

[d_gosbell]

Something like the following should work 

SamePriceLast(5)Transactions = IF (HASONEFILTER(Transactions[SupplierName]),
    var maxDate = MAX(Transactions[PaymentDate].[Date])
    var last5dates = TOPN(5, CALCULATETABLE(Transactions, Transactions[PaymentDate].[Date] <= maxDate), Transactions[PaymentDate])
    return countrows( FILTER( last5dates, [SamePrice True/False] = TRUE())) = 5
,"N/A")

First I check if we are filtered by supplier (ie if supplier is on the rows or a single supplier is selected in a filter)

Then I grab the max date (incase the date is filtered, so if you filter for Jan 2021 this measure will only consider transactions on or before 31 Jan 2021) and then get the top 5 transactions before the max date. From there I count the rows where same price is true and check if that is equal to 5.