Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Been working this for 2 days straight and read countless forum posts and YT vids - no joy still.
I have a Revenue dataset that contains recurring customer revenue - not sales (same customers appear every month).
I want to identify every month how many customers stop generating revenue and how many customers begin generating revenue so I can plot as a stacked column chart.
(i.e. Customer X had revenue last month but not this month = lost customer. Customer Y had no revenue last month but has revenue this month = new customer).
I also need to show a Top 10/Bottom 10 New/Lost Customers by Revenue won/lost each month
SAMPLE DATA: https://www.dropbox.com/s/71o28dww2xq3bkl/Test.pbix?dl=0
Expected Results:
GL_PERIOD_START_DATE | CUSTOMER_NUMBER | USD_BUDGET_RATE |
Jan-21 | AAA | $100 |
Jan-21 | BBB | $500 |
Feb-21 | AAA | $100 |
Feb-21 | BBB | $500 |
Mar-21 | AAA | $100 |
Mar-21 | BBB | $500 |
Apr-21 | BBB | $500 |
May-21 | BBB | $500 |
May-21 | CCC | $300 |
Jun-21 | BBB | $500 |
Jun-21 | CCC | $300 |
In the above example, expected results would be:
Jan-21: 0 new, 0 lost
Feb-21: 0 new, 0 lost
Mar-21: 0 new, 0 lost
Apr-21: 0 new, 1 lost
May-21: 1 new, 0 lost
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
Use the following two measures:
New Customer Count = COUNTROWS(EXCEPT(VALUES(REVENUE[CUSTOMER_NUMBER]),CALCULATETABLE(VALUES(REVENUE[CUSTOMER_NUMBER]),FILTER(ALL(REVENUE),REVENUE[GL_PERIOD_START_DT]<MIN('CALENDAR'[Date])))))
Lost Customer Count = COUNTROWS(EXCEPT(CALCULATETABLE(VALUES(REVENUE[CUSTOMER_NUMBER]),DATEADD('CALENDAR'[Date],-1,MONTH)),VALUES(REVENUE[CUSTOMER_NUMBER])))+0
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Anonymous ,
Use the following two measures:
New Customer Count = COUNTROWS(EXCEPT(VALUES(REVENUE[CUSTOMER_NUMBER]),CALCULATETABLE(VALUES(REVENUE[CUSTOMER_NUMBER]),FILTER(ALL(REVENUE),REVENUE[GL_PERIOD_START_DT]<MIN('CALENDAR'[Date])))))
Lost Customer Count = COUNTROWS(EXCEPT(CALCULATETABLE(VALUES(REVENUE[CUSTOMER_NUMBER]),DATEADD('CALENDAR'[Date],-1,MONTH)),VALUES(REVENUE[CUSTOMER_NUMBER])))+0
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Anonymous , You can try measures like
example measures
here sales measure will be sum(Table[USD_BUDGET_RATE])
MTD = calculate([Sales],datesmtd('Date'[Date]))
LMTD = calculate([Sales],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))
Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , 1,BLANK()))
New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK()))
Retained Customer This Month = if(not(ISBLANK([MTD])) && not(ISBLANK([LMTD])) , 1,BLANK())
refer for more details