Dual Filtering Syntax

[masplin]

I'm trying to work out which customers buy a 1 year licence and then rebuy it 1 year later (actually within 1 year of the expiry). This requires some sort of nested filtering where I find all the distinct customer in the first set and then look how many are in both sets. I searched around a bit and thought i had the answer but doesn't look right as have more in the "both" set than in the 1st set!

Matchinput is a table of invoices with Match Ref being a customer no

1st Set

PCC Buyer=CALCULATE( DISTINCTCOUNT(MatchInput[Match Ref]),
                                                MatchInput[PCC Purchase]="yes"
                                               )

2nd Set

PCC Buyer Next Year=
          CALCULATE( [PCC Buyer],
                             DATESINPERIOD(
                                                      DateTable[Day],
                                                      LASTDATE(
                                                                      DATEADD(DateTable[Day],1,YEAR)
                                                                      ),
                                                          1,
                                                           Year
                                                        )
                             )

These seem to be accurate so I then tried to combine them using some other posts, but obviously hopelessly wrong. If anyone can give me a steer how to filter on Set 1st then apply the Set 2 filter to see who is both that would be really appreciated.

CALCULATE(                        DISTINCTCOUNT(MatchInput[Match Ref]),
                         FILTER(
                                      DISTINCT(MatchInput[Match Ref]),
                                      [PCC Buyer Next Year]>0
                                      )
                      )

 

[scottsen]

Rob (at powerpivotpro) and Marco/Alberto (at SqlBI.com) both have articles on the returning customers patterns. They show up easy with a google for "dax returning customers". Though, if that doesn't get you there... I can probably try thinking hard n stuff

 

[masplin]

That's the stuff I was reading. I'll read it again as obvious misunderstood somewhere.

 

[scottsen]

Somehow haveing a measure that looks into the future is throwing me, I'm apparently a looking back kinda person

Your measure doesn't look too far off what I would expect. I would probably use VALUES instead of DISTINCT (but now that I saw that... I'm kinda curious about the diff). And you didn't filter on PCC Purchase. I would expect:

=CALCULATE([PCC Buyer], FILTER(VALUES(MatchInput[Match Ref]), [PCC Buyer Next Year] > 0))

 

[masplin]

Your the best as seems spot on for the few I've checked .

Thanks a lot