Comparing Snapshots to categorize Customers

nbs333 — Thu, 11 Aug 2022 14:58:05 GMT

I have data that gets loaded monthly. Each month is a snapshot of all active customers. My goal is to compare the most recent snapshot to the same period last year so I can categorize customers. Here is a sample of a scaled down dataset.

I was able to calculate a table that categorizes each customer and then used measures to count each category. The issue is that this approach is not dynamic.

The pbix file attached has my attempt to make the table dynamic. My [YoY Cust Categories] is essentially my calculated table in measure form. I don’t think this approach will work. It is very slow and when I use it in my scaled-up data set I get a memory error.

Any other approach will greatly appreciate.

Data: https://drive.google.com/file/d/1ZbipUeq7psvY9m0_wk55Az7BVWOKp9TU/view?usp=sharing

PBIX: https://drive.google.com/file/d/1Xpei1D6KqRALHcFGKKDWNVj_xJFCdE_X/view?usp=sharing

Re: Comparing Snapshots to categorize Customers

daXtreme — Fri, 12 Aug 2022 13:03:18 GMT

Hi nbs333

Here's how to perform such an analysis properly: Calculate New, Returning, Lost, and Recovered Customers in #dax - SQLBI

topic Comparing Snapshots to categorize Customers in DAX Commands and Tips

Comparing Snapshots to categorize Customers

Re: Comparing Snapshots to categorize Customers