Cumulative distinct count

[joerykeizer]

Hi all,

 I have a table with order, date and customer and I would like to count the number of cumulative orders per customer. 

Currently I have:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Order]),FILTER(ALLSELECTED('Table'),'Table'[Order]<max('Table'[Order]))).

This works but I would like to have it counted per customer without having to filter them. (Expected results: 0-1-2-2-0-1-0-1). In addition, I'd like to know how to do the same thing with a calculated column, since using the formula as is returns only 1 number.

Thx,

Joery

[v-cherch-msft · Accepted solution]

Hi @joerykeizer

You may try to create a measure or column as below:

Measure =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Customer] ),
        Table1[Order] < MAX ( Table1[Order] )
    )
)
    + 0

Column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Order] ),
    FILTER (
        Table1,
        Table1[Customer] = EARLIER ( Table1[Customer] )
            && Table1[Order] < EARLIER ( Table1[Order] )
    )
)
    + 0

Regards,

Cherie

[joerykeizer]

Hi @v-cherch-msft

Thank you! There is one issue though.

This works perfectly in my test-dataset but calculating the column is too heavy in the actual one. Dataset is currently ~155k rows. Is there any way to do this more efficiently?

Regards,

Joery

[v-cherch-msft]

Hi @joerykeizer

You may also try to make a query with M language in Advanced Editor in Query Editor. It seems a more complicated way.

Regards,

Cherie