How to add the amount that has been lost for each item?

[carolinefer]

Hello All,

I have a table with the count of iteams that I have been downgraded, however, I would like to add the amount that has been lost. 

When I add the amount into the table with the down and up flag, I got the wrong values. 

Would you be able to help me with this?

Following the current logic I have, which is downgrade and upgrade is only related to accounts that has a Revenue in previous month and current month, we do not consider new or churn accounts. The downgrade and upgrade is related only to live accounts, meaning that they need at least one product in the current month and previous month to be categorized as downgrade or upgrade. 

Alsom downgrade and upgrade is related to items been add and removed and not only the amount been > than previous month. If account has at least one package in previous and current month and one of the packages has been removed, this should be a downgrade. 

I have already got the number of packages downgrade/upgraded, would you be able to help me getting the amount downgrade/upgraded? thanks in advance

https://drive.google.com/file/d/1Ope_Hr2j-c7RyLm21lbbZdFmu6_ydMsc/view?usp=sharing

[carolinefer]

Hi @lbendlin , I am sorry, did not realize that it was restricted. https://drive.google.com/file/d/1Ope_Hr2j-c7RyLm21lbbZdFmu6_ydMsc/view?usp=sharing Thanks for letting me know 

[lbendlin · Accepted solution]

In order to calculate "things that aren't there"  like your lost customers  you need to create a crossjoin or use disconnected tables. You can create that from existing data, or - better - from external master lists.

In your example you want to see how your customers fare over time

for example : 

Reference = CROSSJOIN(values(Sheet1[customer_id]),VALUES(Sheet1[date]))

Then you can compute your flag for each of the combinations of customer and date.

How would you handle situations like this:  Customer has fewer packages but increased amount:

[lbendlin]

Please change the Google Drive link so it doesn't require signing in.