Forum Discussion
Solved
Duplicate values on time span
Hello! I have a selection of customer names and dates of visits. In both columns the data can be repeated. I need to identify those customers who visited cafe more than one time in 14 days. I can't f...
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
Thank you very much for the answer. But the fact is that there can be more than two visits in 14 days and even several in one day, that is, the dates will be repeated.
-- calculated column without the use of context transition
[At Least 2 Visits Within 14 Days] =
var __custId = C[CustomerID]
var __visitDatesWithCounts =
ADDCOLUMNS(
SUMMARIZE(
FILTER (
V,
V[CustomerId] = __custId
),
V[VisitDate]
),
"CountOfSameDayVisits",
var __visitDate = V[VisitDate]
return
COUNTROWS(
FILTER (
V,
V[CustomerId] = __custId
&& V[VisitDate] = __visitDate
)
)
)
var __2VisitsOnSameDayExist =
MAXX(
__visitDatesWithCounts,
[CountOfSameDayVisits]
) > 1
var __2VisitsOnDiffDaysExist =
NOT ISEMPTY(
FILTER(
CROSSJOIN(
SELECTCOLUMNS (
__visitDatesWithCounts,
"FirstVD", V[VisitDate]
),
SELECTCOLUMNS(
__visitDates,
"SecondVD", V[VisitDate]
)
),
[SecondVD] - [FirstVD] < 14
&& [SecondVD] > [FirstVD]
)
)
return
__2VisitsOnSameDayExist || __2VisitsOnDiffDaysExistBest
Darek