item no wise Last 3 Transaction filter with posting date

[Anonymous]

Item wise select last 3 transaction from posting date filter ,below mentioned Example Actaul record and excepted answer record

Example:

Actual Record:

Item No	Posting date
A	28-11-19
A	28-11-19
A	31-11-19
A	02-12-19
B	23-10-19
B	24-10-19
B	23-10-19
B	01-03-20
C	24-12-19
C	24-12-19
C	25-12-19
C	26-12-19

Expected Answer:

If Select posting date it is should apprear last 3 transaction details item no Wise,it is should remove Duplicate entry also

Posting Date is 26-12-2019

A	27-11-19
A	28-11-19
A	02-12-19
B	23-10-19
B	24-10-19
B	25-10-19
C	24-12-19
C	25-12-19
C	26-12-19

Advanced Thanks 

Kamal

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

First you need to create a date table that is not associated as a slicer.
Then you need to enter "edit queries" to create an index column as the sorting condition, and you need to sort the date before creating the index column.
Then create the measure and apply the filter

Measure =
    IF (
        MAX ( test[Posting date] ) <= SELECTEDVALUE ( 'Table'[Posting date] ),
        RANKX (
            FILTER (
                ALLEXCEPT ( test, test[Item No] ),
                test[Posting date] <= SELECTEDVALUE ( 'Table'[Posting date] )
            ),
           [measure2],
            ,
            DESC
        )
    )

You can also refer to the pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks  for your reply

if i Select posting  date filter from posting date it is should show last 3 posting date Transaction  details and remove duplicate entry also Below mention actual data and  expected data  

Actual data

Item No     Posting Date
A                  01-01-19
A                  01-01-19
A                 03-01-19
A                04-01-19
A               05-01-19
A               06-01-19
A              07-01-19
A             08-01-19
A             09-01-19
A           10-01-19
B            01-01-19
B            02-01-19
B            02-01-19
B           04-01-19
B         05-01-19
B         06-01-19
B         07-01-19

Expected Answer

If  select Posting date  05-01-2019

Item No   Posting date

A                03-01-19
A               04-01-19
A               05-01-19

B              02-01-19
B             04-01-19
B              05-01-19

[amitchandak]

Create a new column in table

Rank = RANKX(all(Table[Item No]),Table[Posting date],,DESC,Dense)

and then filter for rank <=3

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin