Forum Discussion
Duplicate values on time span
- Anonymous7 years ago
I think this is what you need:
Find the names of the customers that have 2 different visit dates not more than 14 days apart.
Let's say your table that stores visits is V, you've got CustomerID and VisitDate in there. Then you also have a dimension table with your customers C where you store unique CustomerID's. C joins to T in a 1:many fashion on CustomerID. Then you could add a column [2 Visits Within 14 Days] to C:
[2 Visits Within 14 Days] = -- calculated column without the use of context transition
var __custId = C[CustomerID]
var __visitDates =
SUMMARIZE(
FILTER (
V,
V[CustomerId] = __custId
),
V[VisitDate]
)
var __2VisitsExist =
NOT ISEMPTY(
FILTER(
CROSSJOIN(
SELECTCOLUMNS (
__visitDates,
"FirstVD", V[VisitDate]
),
SELECTCOLUMNS(
__visitDates,
"SecondVD", V[VisitDate]
)
),
[SecondVD] - [FirstVD] < 14
&& [SecondVD] > [FirstVD]
)
)
return
__2VisitsExistBest
Darek
What does it mean "more than 1 time in 14 days"? Which 14 days? Where is the beginning and where is the end of the 14 days?
Best
Darek
It should be a cycle that checks every 14 days from the first date. That is, from 01.06 to 14.06, then from 02.06 to 15.06, and so on. I need a list of customers who visited cafe more than once in 14 days at any time.