Get last value for each category but date slicer friendly

[Anonymous]

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!

[tamerj1]

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 

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

For the tables use this kind of logic:

Measure 18 =

var Ldate = LASTDATE('Table (7)'[PURCHASE DATE]) return

calculate(MAX('Table (7)'[ITEM]),'Table (7)'[PURCHASE DATE]=Ldate)

Measure 19 = LASTDATE('Table (7)'[PURCHASE DATE])

For the graph:

Measure 20 =

var client = MAX('Table (7)'[CLIENT NUMBER])

var Ldate = CALCULATE(MAX('Table (7)'[PURCHASE DATE]),ALL('Table (7)'),'Table (7)'[PURCHASE DATE]<=MAX('Calendar'[Date]),'Table (7)'[CLIENT NUMBER]=client) return

if(MAX('Table (7)'[PURCHASE DATE])=Ldate,1,0)

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/

[Anonymous]

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?

[ValtteriN]

Hi,

Here is a snippet of the visual: