Calcualting repeat cusotmers for each month and then totaling it up for all months

[syedshah01]

Hi,

I have a requirement where for each month starting six months ago until current selected month, it should calculate the repeat customers that purchased in any previous months and then total up the repeat customers for all six months. For example

There are ten customers, A, B, C, D, E, F, G, H, I, J. Customer A, B, C, D and E purchased a product in December. In January, we have D, E, F cusotmers so we have two repeat customers. In Feb, A, H and I purchased. Since A, purchased previously (Dec), we have 1 repeat cusotmer in Feb. In March, we have B, E, H and J so we have three repeat customers and so on. We calculate this until current month. Eventually, we total up the repeat for all months.

How can I calcualte this using DAX?

[Anonymous · Accepted solution]

Hi @syedshah01 ,

I create a table as you mentioned.

Then I create three measures and here are the DAX codes.

12m-1m = 
CALCULATE(
    DISTINCTCOUNT('Table'[CustomerID]),
    FILTER(
        'Table',
        (
            NOT(ISBLANK('Table'[12m])) && NOT(ISBLANK('Table'[1m]))
        )
    )
)

12m-2m = 
CALCULATE(
    DISTINCTCOUNT('Table'[CustomerID]),
    FILTER(
        'Table',
        (
            NOT(ISBLANK('Table'[12m])) && NOT(ISBLANK('Table'[2m]))
        ) || (
            NOT(ISBLANK('Table'[1m])) && NOT(ISBLANK('Table'[2m]))
        )
    )
)

12m-3m = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[CustomerID] ),
    FILTER (
        'Table',
        ( NOT ( ISBLANK ( 'Table'[12m] ) ) && NOT ( ISBLANK ( 'Table'[3m] ) ) )
            || ( NOT ( ISBLANK ( 'Table'[1m] ) ) && NOT ( ISBLANK ( 'Table'[3m] ) ) )
            || ( NOT ( ISBLANK ( 'Table'[2m] ) ) && NOT ( ISBLANK ( 'Table'[3m] ) ) )
    )
)

Finally you can count all the measures.

Total = 'Table'[12m-1m] + 'Table'[12m-2m] +'Table'[12m-3m]

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @syedshah01 check link with example