Present products on last visit

[jony_gloeckner]

Hello Community,

I need help with one complex DAX formula.

I want to know how many locations have product marked with IsPresent=1 on last visit from sales rep.

Sales rep visits are tracked with ID and DistDate columns.

Tricky part is that formula needs to be calculated dynamically from maximum selected date on date slicer and calculate the results only for period of maximum selected date – 30 days. 

Here is table with data.

We have next columns:

- ID -> uniqe visit ID - this column has uniqe visit id written down in integer. This column can also be used for finding out which is the last visit

- CustomerID - this is location of product

- DistDate - date when visit happened

- UserID - Sales rep code

- ProductID - Product code

- isPresent - flag that sales rep gives to product if present on location

Here is table with data: 

ID	CustomerID	DistDate	UserID	ProductID	isPresent
36094	CI2995	2024-01-25	P111	101	1
33027	CI2995	2024-01-11	P111	101	0
37326	C1902356	2024-01-31	P113	115	0
38261	C1902357	2024-02-05	P113	115	1
36695	C1902357	2024-01-29	P114	115	1
35110	C1902357	2024-01-22	P112	115	0
32237	C1902357	2024-01-08	P112	115	1
38016	C1902375	2024-02-02	P112	115	0
35814	C1902376	2024-01-24	P111	115	1
37657	C1902377	2024-02-01	P111	115	0
34734	C1902377	2024-01-18	P111	115	0
35295	C1902378	2024-01-23	P111	115	1
32548	C1902378	2024-01-10	P111	115	1
37694	L3565025	2024-02-01	P111	115	0
34694	L3565025	2024-01-18	P111	115	1
37996	L3565026	2024-02-02	P111	115	1
35729	L3565028	2024-01-24	P111	115	1
37083	D3565029	2024-01-30	P111	115	1

How would you approach this issue?

[AMeyersen · Accepted solution]

Hi @jony_gloeckner ,

this sounds slightly complicated, so I'd make excessive use of table variables to keep track of what I'm doing.

locationsProductPresent =

-- get the correct date range
VAR _endDate =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR _startDate = _endDate - 30
VAR _potentialVisits =
    FILTER ( 'Table', [Date] >= _startDate && [Date] <= _endDate ) 

-- get a table of last visit per location and product
VAR _lastVisitPerProduct =
    SUMMARIZE (
        _potentialVisits,
        [CustomerID],
        [ProductID],
        "@maxVisitID", MAX ( 'Table'[ID] )
    ) 

-- get a table of last visit per location and product when the product was present
VAR _lastVisitPresent =
    SUMMARIZE (
        FILTER ( _potentialVisits, [isPresent] = 1 ),
        [CustomerID],
        [ProductID],
        "@maxVisitID", MAX ( 'Table'[ID] )
    ) 

-- only keep identical rows of both tables (last visit overall AND last visit when product was present)
VAR _isPresentOnLastVisit =
    INTERSECT ( _lastVisitPerProduct, _lastVisitPresent ) 

-- get a list of distinct location which satisfy your critera
VAR _locations =
    SUMMARIZE ( _isPresentOnLastVisit, [CustomerID] ) 

-- count number of locations
RETURN
    COUNTROWS ( _locations )

You might have to tweak it a bit to get the correct results, but the logic should work.

This approach might not work in very large models due to performance issues. In this case you are probably forced to do some pre-calculations in PowerQuery to reduce query runtime.