How to match customer IDs between months and return a decrease or increase in sales

[Kayslam]

I will really appreciate help with this.

I have 12 months of data. I have columns containing unique customer IDs, location and and sale units. I want to compare the change in units by mactching the customer IDs from month to month. 

(1) I'm particularly looking at the "decrease in units for existing customers", and the "increase in units for existing customers and new customers"

(2) And return a distinct location count for the two scenarios above.

In short:

1. How many sale units have been added from the previous month (additions could come from new and existing customers)?

2. How many location by distinct count did the additions come from?

3.What was the decrease in sale units from the previous month by existing customer ID ? 

(A decrease could happen by loosing a customer entirely or just lost of partial sale)

4. How many locations by distinct count did the decrease come from?

Table Columns:

Date         Customer ID       Revenue       Units        Location

Expected Results:

Table columns (Units)

Month(YY-MM)      Beginning Units    Units Added      Units lost      Net Units     Ending Units

Table Columns (Locations)

Month(YY-MM)      Beginning Locations    Locations Added      Locations lost      Net Location      Ending Locations

I was able to get the units and locations added but it didn't work for the units and locations lost on the other way round.

CALCULATE([VARIANCE],FILTER(SUMMARIZE('Sales','Sales'[customer_id]),[VARIANCE] > 0))

CALCULATE(COUNTROWS(SUMMARIZE('Sales','Sales'[customer_id])),FILTER(SUMMARIZE('Slaes','Sales'[customer_id]),'Sales'[VARIANCE] > 0))

CALCULATE([UNITS SUM], DATESMTD('Date'[Date]))

CALCULATE([UNITS SUM], DATESMTD(DATEADD('Date Table'[Date],-1,MONTH)))

[v-binbinyu-msft]

Hi @Kayslam ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[Kayslam]

Thanks @v-binbinyu-msft  I have modified my post. I hope this helps. I'm happy to clarify as needed. Thanks again for your help.