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, bu...
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.

    :->

tex628's avatar
tex628
Community Champion
7 years ago

Is it possible to for you take a picture of the timestamps and the calculated column? 

Like this? 


Edit: Ignore the 2's in the picture!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Yea sure, it's either 0 or 1, there are no other numbers

 

  • tex628's avatar
    tex628
    Community Champion
    7 years ago

    Thats really weird. 

    If you filter on a single returning [did] and then sort by acending dates, how does it look?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I forgot to mention that there are "groups" like "Mc Donalds", "KFC" etc. So if a person (did) visits a KFC and next day Mc Donalds, with the same ID, its not a returning customer. It has to be in the same group within 2 different days. I can't take a sceenshot since the table is very big and the columns are too far away.

       

      But this is how you can imagine in.

       

      only did 1 would count as ONE returning customer in this table. So in this case the result of the measure would be 1 for the given data.

       

       

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

       

       

      • tex628's avatar
        tex628
        Community Champion
        7 years ago

        In that case, make a new column with [did] and [groups]:

        did_groups = [did] & [Groups]


        Then replace [did] with the new column in the measure below.

        This should reduce the amount of returning customers that ur getting.

        Column = 

VAR ID = [did_group]
VAR DATE = [ltime]

return

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