Attrition Analysis

arch.matef · April 28, 2023, 12:44am

Hello,

i need to build report as shown in the Attrition Analysis: Finding Lost Customers Using Power BI & DAX

i tried to follow the same steps in the blog, but without luck its alweys showing there is a problem specially in the second part

i used this DAX formula for the lost costumers count

Lost Customers = 
VAR CustomerPurchased = CALCULATETABLE(DISTINCT('Full Sales Report'[ship-to party]),
                            FILTER(ALLSELECTED('Full Sales Report'),
                                'Full Sales Report'[billing date] >= MAX('Full Sales Report'[billing date]) - 120 &&
                                'Full Sales Report'[billing date] < MAX('Full Sales Report'[billing date]) - 30 ))
VAR PriorCustmores = CALCULATETABLE(DISTINCT('Full Sales Report'[ship-to party]),
                            FILTER(ALLSELECTED('Full Sales Report'[billing date]),
                                'Full Sales Report'[billing date] > MAX('Full Sales Report'[billing date]) - 30 ) )

RETURN
COUNTROWS(EXCEPT(CustomerPurchased, PriorCustmores)) * -1

im receiving the below result but still im not sure if this is correct or not ?

when i tried to apply the second part as below code

Lost Customers QTY = 
VAR CustomerPurchased = CALCULATETABLE(DISTINCT('Full Sales Report'[ship-to party]),
                            FILTER(ALLSELECTED('Full Sales Report'),
                                'Full Sales Report'[billing date] >= MAX('Full Sales Report'[billing date]) - 120 &&
                                'Full Sales Report'[billing date] < MAX('Full Sales Report'[billing date]) - 30 ))
VAR PriorCustmores = CALCULATETABLE(DISTINCT('Full Sales Report'[ship-to party]),
                            FILTER(ALLSELECTED('Full Sales Report'[billing date]),
                                'Full Sales Report'[billing date] > MAX('Full Sales Report'[billing date]) - 30 ))

RETURN
CALCULATE(CALCULATETABLE('Full Sales Report',
DATESBETWEEN('Full Sales Report'[billing date], (MAX('Full Sales Report'[billing date]) - 120), (MAX('Full Sales Report'[billing date])-30))),
EXCEPT(CustomerPurchased, PriorCustmores
))

but always return with this error

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Monthely POP - Copy.pbix (1.3 MB)

please let me know how can i fix it

Keith · April 28, 2023, 1:00am

Hi @arch.matef

I think your problem is that you aren’t using a separate date table. Best practice is to use a EDNA Date Table within your model.

here is the link
Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

The link that you stated is using the EDNA Date Table not the fact table dates

Make sure you mark your table as a date table.

thanks
Keith

arch.matef · April 28, 2023, 8:13pm

Hello Keith, i would like to thank you for your valuable reply, and i already implement the date table as shown in the youtube, im receiving now the below result

another point there is 3 months extras shown in the table and i didn’t got the point why these extra months, regarding the loss quantities its seems the last 3 months cumulative quantities if i need the average loss quantities

and please if there is any links or topics you can referee to me to got the exactly lost clients for each selected month as shown below

Keith · April 28, 2023, 10:33pm

Hi @arch.matef,

I kinda remember there is something about cumulative quantities after a certain period that you don’t need right because its in the future.

Right off hand, I can’t find it.

Please review the courses within the EDNA or youtube might have something too.

I’ll try to find it but i won’t have time to do it.

thanks
Keith

EnterpriseDNA · May 3, 2023, 10:11am

Bumping this post for more visibility from our experts and users.

ankit · May 4, 2023, 4:48am

Hi @arch.matef - Can you share the latest PBIX file. I will check the first file.

Thanks
Ankit J

ankit · May 4, 2023, 6:37am

Hi @arch.matef - Test below formula and also refer to these posts.

Lost Customers_Ankit = 
VAR CustomerPurchased = CALCULATETABLE(DISTINCT('Full Sales Report'[ship-to party]),
                            FILTER(ALLSELECTED('Full Sales Report'),
                                'Full Sales Report'[billing date] >= Max('Full Sales Report'[billing date]) - 120 &&
                                'Full Sales Report'[billing date] < Max('Full Sales Report'[billing date]) - 30 ))
VAR PriorCustmores = CALCULATETABLE(DISTINCT('Full Sales Report'[ship-to party]),
                            FILTER(ALLSELECTED('Full Sales Report'[billing date]),
                                'Full Sales Report'[billing date] > MIN('Full Sales Report'[billing date]) - 30 && 'Full Sales Report'[billing date] <= MIN('Full Sales Report'[billing date])) )

RETURN
COUNTROWS(EXCEPT(CustomerPurchased, PriorCustmores)) * -1

Thanks
Ankit J

EnterpriseDNA · May 4, 2023, 9:40am

Hello @arch.matef

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

arch.matef · May 4, 2023, 9:46am

Hello @ankit

Thanks a lot for your detailed reply, much appreciated

arch.matef · May 8, 2023, 7:29am

Hello @EnterpriseDNA
im trying to reach these links but alweys showing that i need to enroll to access the data

Keith · May 8, 2023, 11:13am

the links are no longer available but if you go to the new portal under the webinar section or the learning center section

courses or webinar and search for the topic that you are looking for.

search area is in the top right hand corner of the dashboard

thanks
Keith