Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

returning customer count

Hey folks,

 

I know that this topic was already discussed, but I didn't get any of the solutions to work that were posted here before. I was able to solve my problem with 2 dax tables + measure, but I'm not happy with my solution and I guess that there is a better / simpler way to do it.

 

So let's take a look:

 

Imagine you have this type of data: an id, longitude, latitude (both does not really matter here) and date + time stamp.

 

did	lon	lat	ltime
1	8.89314365386963	49.2425270080566	2018-08-02 20:58:12
1	8.89325141906738	49.2425804138184	2018-08-02 20:53:54
1	8.89325523376465	49.2425384521484	2018-08-02 21:00:50
1	8.89333343505859	49.2424774169922	2018-08-04 18:26:42
2	8.89333820343018	49.2424545288086	2018-07-30 20:08:41
3	8.89305019378662	49.2425384521484	2018-08-03 13:31:26
3	8.8930549621582	49.2425498962402	2018-08-03 13:30:27
3	8.89305782318115	49.242618560791	2018-08-03 13:28:25
3	8.89305782318115	49.2426338195801	2018-08-03 13:27:24
3	8.89306926727295	49.2425994873047	2018-08-03 13:29:26
3	8.89320182800293	49.2427368164063	2018-08-03 13:26:24
3	8.89355945587158	49.2428245544434	2018-08-03 13:25:32
3	8.89356231689453	49.2427139282227	2018-08-03 13:23:31
3	8.8935661315918	49.2427444458008	2018-08-03 13:24:22

 

What I want to do is: count how many people (distinct did) do exist with a different timestamp (based on 2 different days). If we take a look at did 1 - the first 3 rows count as one visit, the 4th row is a different day (timestamp does not matter) so it also counts as a visit. So this guy visited 2 times ( or >1) and represtens a returning customer. did 2 visited only once, so it does not count as a returning customer. did 3 visited the place several times (or stayed there a bit longer), but it was on the same day, so it does not count as a returning customer. So basically for that given data the measure would return 1. We have only 1 returning customer. I hope you did understand the problem.

 

My solution: first dax table

 

countPerDay = CALCULATETABLE (
 SUMMARIZE (
 KraichgauNord;
 KraichgauNord[did];
 'Date'[Date];
 "returning customer"; COUNTROWS ( KraichgauNord )
 )
)
the table returns the did, the date and the count of visists during that single day. So it's basically a group by did and date.
 
2nd step was to create the 2nd dax table:
 
returner = 
    SUMMARIZE (
       countPerDay ;
        countPerDay[did];
        "real returning customers"; COUNTROWS (countPerDay)
    )
The 2nd table groups the 1st table by did. If you have a count of 1, the person has been once there on a single day. If it has a value of > 1, the person visited twice or even more on DIFFERENT days.

And finally I've made a measure which looks like this:
ct_returner = 
var ct = CALCULATE(COUNTROWS(returner); returner[real returning customers] > 1)
return IF (ct = 0; 0; ct)
It seems to be so complicated.. I had to create 2 dax tables + measure for the simple count if a did exists more than once per day in a table.
 
Any ideas?
Need Help
  • tex628's avatar
    tex628
    7 years ago

    This is really confusing. 

    I've made my own example dataset:

     



    The calculated column "Column" is simply:

    Column = 
VAR ID_ = [did_groups]
VAR DATE_ = [ltime]

return

IF(CALCULATE( SUM([lon]);
ALL(Table1);
Table1[did_groups] = ID_;
table1[ltime] < DATE_
)
>0
;1;0)

    Which gives me 7.

    Then i use the measure:

    Measure = Calculate(distinctcount([did_groups]);Table1[Column]=1)

    Which removes 1 and give's me 6. Which should be the correct number of returning customers in the dataset that I made.

    :->

15 Replies

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

    Convert ltime to date instead of datetime.
    Calculated column: 

    Column = 

VAR ID = [did]
VAR DATE = [ltime]

return

IF(CALCULATE( SUM([lon]);
ALL("YOURTABLE");
[did] = ID;
[ltime] < DATE
)
>0
;1:0)

    Measure:

    Measure = Calculate(distinctcount([did]);column=1)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for your reply, but the calculated column doesn't seem to work.

      Edit: I've corrected ":" to ";", but there still seems to be a problem with the definition of variables.

      • tex628's avatar
        tex628
        Community Champion
        7 years ago

        Right, it doesnt let you name things to those specific names :p Just rename them and change the ":" at the end!