Forum Discussion
item no wise Last 3 Transaction filter with posting date
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
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 filterMeasure =
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.
3 Replies
- amitchandak
Super User
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 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-19Expected Answer
If select Posting date 05-01-2019
Item No Posting date
A 03-01-19
A 04-01-19
A 05-01-19B 02-01-19
B 04-01-19
B 05-01-19
V-lianl-msftCommunity Support
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 filterMeasure =
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.
