Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |