Count status of item on latest occurrence

[Anonymous]

Hi All,

Brief : There are multiple visits to a particular store in week/month for different kind of item checks by a person.

Visit Date	Item	Store	Item Present/Absent	Week
1-Jan-21	A	ABC	Absent	1
2-Jan-21	B	CDF	Present	1
3-Jan-21	B	XYZ	Present	1
4-Jan-21	A	FMS	Present	1
29-Jan-21	A	ABC	Present	4
31-Jan-21	B	CDF	Absent	4
1-Feb-21	B	PLO	Absent	5
1-Feb-21	B	CDF	Present	5
2-Feb-21	A	ABC	Present	5
3-Feb-21	A	GHY	Present	5

Above is the scenario. Consider week as calendar weeks (data here might be different , for logic puspose I have considered 1 Jan 2021 as week 1).

CASE 1 :
User Selects Jan in time slicer.
Expected Output is

Item	Present in Stores	Absent in Stores	Total Unique Stores for Item
A	1		1
B	1	1	2

This needs to happen dynamically as in , when multiple months are selected only the latest instance of unique visit(Item+Date+Store) should be considered for absent or present calculation.
Latest visit may not necessarily fall on last date of selected period.


CASE 2 : 
User selects Jan and Feb in slicer
Expected Output

Item	Present in Stores	Absent in Stores	Total Unique Stores for Item
A	3		1
B	2	1	3

Kindly help.

[v-angzheng-msft]

Hi, @Anonymous 

How to get the desired result? Could you please consider sharing more details about it?

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

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

[AllisonKennedy]

@Anonymous The LASTDATE() function should help you here. Note it will use the filters you have selected so will show the last date that the product/store has a row in the table. 

[Anonymous]

LastDate() did not seem to fulfil the requirement. But Calculating a virtual table with MAXDATE function seems to work. Thanks for your time . Appriciate it.