topic Re: Calcualting repeat cusotmers for each month and then totaling it up for all months in DAX Commands and Tips

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

syedshah01 — Tue, 09 Jul 2024 19:42:50 GMT

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?

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

some_bih — Thu, 11 Jul 2024 05:33:36 GMT

Hi syedshah01 check link with example

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

Anonymous — Tue, 23 Jul 2024 07:26:00 GMT

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.