Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
Here is my situation : I would like to create a sectoral visual but only showing the last item purchased by each of my customers, depending on a date slicer. So let's say I have this table :
ROW ID | CLIENT NUMBER | PURCHASE DATE | ITEM |
1 | A | 01/01/2022 | Flowers |
2 | B | 02/01/2022 | Clothes |
3 | A | 15/01/2022 | Chocolate |
4 | A | 01/02/2022 | Candy |
5 | B | 13/02/2022 | Kitchenware |
6 | C | 15/02/2022 | Soap |
7 | C | 27/02/2022 | Gameboy |
If i'm selecting a range period of time between 01/01/2022 and 31/01/2022, I need the table to show :
ROW ID | CLIENT NUMBER | PURCHASE DATE | ITEM |
2 | B | 02/01/2022 | Clothes |
3 | A | 15/01/2022 | Chocolate |
If I'm selecting a range period of time between 15/01/2022 and 27/02/2022, I need the table to show :
ROW ID | CLIENT NUMBER | PURCHASE DATE | ITEM |
4 | A | 01/02/2022 | Candy |
5 | B | 13/02/2022 | Kitchenware |
7 | C | 27/02/2022 | Gameboy |
And if I'm selecting the whole period, it should show :
ROW ID | CLIENT NUMBER | PURCHASE DATE | ITEM |
4 | A | 01/02/2022 | Candy |
5 | B | 13/02/2022 | Kitchenware |
7 | C | 27/02/2022 | Gameboy |
The idea would be to then have the sectoral visual, using the "client number" as values and "item" as detail :
I've tried many measures to apply them as filters on my visual but nothing seems to work. I would be very grateful if you can help me on this one!
Solved! Go to Solution.
Hi,
Here is one way to do this:
For the tables use this kind of logic:
For the graph:
Note that I don't have a relationship between calendar and the test table. Here I use calendar for slicer.
ping me with @ if you have questions.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Hi @Anonymous
Here is the sample file with solution https://www.dropbox.com/t/Yj42dvuDm4iOC6R7
You can use the following code for all measures just change the name of the column
Last Item =
VAR LastVisibleDate = MAX ('Date'[Date] )
VAR Result =
CALCULATE (
SELECTEDVALUE ( Data[Item] ),
FILTER (
Data,
Data[PURCHASE DATE] = LastVisibleDate
)
)
RETURN
Result
Please let me know if you have any further question.
If my reply answers your query please mark it as accepted solution. Thank you
Hi,
Here is one way to do this:
For the tables use this kind of logic:
For the graph:
Note that I don't have a relationship between calendar and the test table. Here I use calendar for slicer.
ping me with @ if you have questions.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Hello @ValtteriN and thank you for your answer.
I've tried reproducing your visual but I can't make it work. Can I see in which fields did you put the measures?
Hi,
Here is a snippet of the visual:
Proud to be a Super User!