'Is this a first purcghase' formula not working

[IanR]

Hi,

I am trying to report on the number of customers in a given period that are first time customers. To do this I thought I would create a column on the fact table that contained a ‘watches owned so far’ value. I can then count the rows where this value is more than zero in each time period.

We sell watches and the ‘fact’ table in this instance, tcrmb_watchesowned, is small.

The formula, which I have based on a similar formula in one of my Russo/Ferrari books, is below. It runs without error and at first glance the customers with the most watches end up with the higher values but those values are far too high, sometimes as much ten times too high.

Can anybody see what I am doing wrong?

Thanks in advance.

NumWatchesOwned =
VAR
    CurrPurchDate = tcrmb_watchesownedSet[DateOfPurchase]
VAR
    CurrCustID = tcrmb_watchesownedSet[tcrmb_Customer.Id]
RETURN
    COUNTROWS (
          FILTER (  tcrmb_watchesownedSet,
                    tcrmb_watchesownedSet[tcrmb_Customer.Id] = CurrCustID
                    &&
                    tcrmb_watchesownedSet[DateOfPurchase] < CurrPurchDate
                    &&
                    tcrmb_watchesownedSet[tcrmb_Customer.Id] <> BLANK()
                )
    )

[Anonymous · Accepted solution]

See if you get a better result with:

NumWatchesOwned =
VAR
    CurrPurchDate = tcrmb_watchesownedSet[DateOfPurchase]
VAR
    CurrCustID = tcrmb_watchesownedSet[tcrmb_Customer.Id]
RETURN
CALCULATE(
    COUNTROWS (tcrmb_watchesownedSet),
    ALL(tcrmb_watchesownedSet),
    tcrmb_watchesownedSet[tcrmb_Customer.Id] = CurrCustID
    tcrmb_watchesownedSet[DateOfPurchase] < CurrPurchDate
    not ISBLANK(tcrmb_watchesownedSet[tcrmb_Customer.Id])
)

[IanR]

Hi Ross,

I had to add some commas into your conditions list but otherwise it worked a treat, thank you.

Sadly, I have to admit that when I tested yours I found that it gave exacly the same resuilts as mine and that mine had been correct all along. I made the mistake of checking the results in a table the report area and forgot that the default beviour there is to aggregate the data. Doh!

In my humble opinion the real problem here is that the Data view, where you are supposed to be able to look at the data, is a real pain to work with. You can't filter anything and frequently the scroll bars take on a malevolent life of their own. If that view was easier to work with I probably wouldn't have tried to view my data in the Report area.

Thanks