How to track volumes from new sales per year

[clivebredenkamp]

Hi,

I would like to track the first time uptake of a product by a customer in a given year e.g. 2018. The idea is to see what volumes are coming in for a product for the first time from a customer. In other words what volume of transactions in 2018 is due to new sales.

Source data

Company CompanyID Product TransactionDate

ABC	1	ProdA	2017/01/17
ABC	1	ProdB	2018/02/03
ABC	1	ProdC	2017/03/01
XYZ	2	ProdA	2017/02/01
XYZ	2	ProdA	2017/03/01
XYZ	2	ProdA	2017/04/01
DEF	3	ProdC	2018/03/01
DEF	4	ProdB	2018/04/01

Desired Results

Product FirstTimeUsage2018

ProdA	0
ProdB	2
ProdC	1

Any help would be appreciated.

[v-chuncz-msft · Accepted solution]

@clivebredenkamp,

You may add the following measure.

Measure =
COUNTROWS (
    EXCEPT (
        CALCULATETABLE (
            VALUES ( Table1[Company] ),
            YEAR ( Table1[TransactionDate] ) = 2018
        ),
        CALCULATETABLE (
            VALUES ( Table1[Company] ),
            Table1[TransactionDate] < DATE ( 2018, 1, 1 )
        )
    )
)
    + 0