Forum Discussion

joerykeizer's avatar
joerykeizer
Helper II
8 years ago
Solved

Cumulative distinct count

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'[Ord...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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