Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MaryDay
Frequent Visitor

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 figure out how to do this. Please, help. Example:

 

11.06.2012    Mark  +

15.06.2012    John   -

17.06.2012   Mark   +

17.06.2012   Sam    +

29.06.2012   Sam    +

05.06.2012   Piter    -

05.06.2012   John    -

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
    __2VisitsExist

 

 

Best

Darek

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

@Anonymous  maybe you have some ideas? please 

Anonymous
Not applicable

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
    __2VisitsExist

 

 

Best

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.

Anonymous
Not applicable

-- 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 || __2VisitsOnDiffDaysExist

Best

Darek

Anonymous
Not applicable

My code does not tell you that there were 2 visits within 14 days but that there were AT LEAST 2 different dates no more than 14 days apart on which the person visited. This is how I understood your description.

 

If you need something different, then you could try to adjust the code. From what you've replied it looks like you don't want to check if there were at least 2 different days within a 14-day span but that there were at least 2 visits regardless of whether on the same day or not.

 

Can you please confirm?

 

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. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors