Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
carolinefer
Helper I
Helper I

Calculate the quantity of each product that have been downgraded/removed from customer account

Hello All, 

 

I have a table with products that I need to know how many customers have removed a certain item from their package.

I tried the following formula but did not work: 

Isdowngrade = IF ( and([prior customer]= 1 , ISBLANK([Current Customer])), 1 , BLANK()) 
 
carolinefer_0-1638971881076.png

 

I also want to remove the customer ID because I am only interested in knowing the total of packages removed. 

 

This would also apply for the upgrades: 

If customer never had that product and has just acquired: 

Isupgrade = IF ([Current Customer] = 1 && ISBLANK([prior customer] && [Days since last purchase] > 0 ), [Current Customer], BLANK())
 
It is not showing me the right number of total packages per month that have been removed or upgraded. would appreciate your help. Thanks 

 

 

1 ACCEPTED SOLUTION
carolinefer
Helper I
Helper I

@lbendlin  that is just amazing! thank you so much for looking into this. It is a very good solution.  I will download to excel to find the total of customer per month! 🙂 

View solution in original post

7 REPLIES 7
carolinefer
Helper I
Helper I

@lbendlin  that is just amazing! thank you so much for looking into this. It is a very good solution.  I will download to excel to find the total of customer per month! 🙂 

carolinefer
Helper I
Helper I

@lbendlin did I provide enough information? Thanks 

Here's what I have so far:

1. Create a reference table for the cross Join

Reference = CROSSJOIN(VALUES(bidowngrade[package]),VALUES(bidowngrade[date]))

2. Add the required measures

Additions = 
var c= SELECTEDVALUE(Reference[date])
var p= CALCULATE(max(Reference[date]),Reference[date]<c)
var cv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=c,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
var pv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=p,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
return if(ISBLANK(pv) && not ISBLANK(cv),1,0)

Deletions = 
var c= SELECTEDVALUE(Reference[date])
var p= CALCULATE(max(Reference[date]),Reference[date]<c)
var cv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=c,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
var pv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=p,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
return if(ISBLANK(cv) && not ISBLANK(pv),1,0)

Increases = 
var c= SELECTEDVALUE(Reference[date])
var p= CALCULATE(max(Reference[date]),Reference[date]<c)
var cv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=c,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
var pv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=p,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
return if(cv>pv,1,0)

Decreases = 
var c= SELECTEDVALUE(Reference[date])
var p= CALCULATE(max(Reference[date]),Reference[date]<c)
var cv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=c,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
var pv=CALCULATE(sum(bidowngrade[amount_usd]),bidowngrade[date]=p,TREATAS(VALUES(Reference[package]),bidowngrade[package]))
return if(pv>cv,1,0)

3. Add a cleanup filter for rows without data

Changed = [Additions]+[Decreases]+[Deletions]+[Increases]

4. Apply the filter

 

This kinda works but is very slow.  I may need to rethink my approach.  See attached.

 

 

 

carolinefer
Helper I
Helper I

Hi @lbendlin, this should also ben counted as downgrade or upgrade. Hope it is possible. Thank you for looking after it! 

carolinefer
Helper I
Helper I

Hi @lbendlin , 

 

Thank you so much for looking into this. 

This is the sample file: https://drive.google.com/file/d/1moc4Wfji52Gklmf-T2Q1wzVN4FHBBcIj/view?usp=sharing

The outcome is :

DatePremier DowgradeEssencial DowngradePayment Processing Downgrade  
Oct 202151015  
Nov 202181012  

 

The goal is to count how many packages have been added or removed per month. 

 

I look forward to hearing from you. 

thank you for the sample data. I see that some packages have changed value from one month to the other - how should that be represented?

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors