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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help understanding Dax (Lost Customers)

Hi,  

 

I need a calculation that works out which customers have not spent with us this year.

 

I have tried to copy some code from the internet (Dangerous) but it does not work

 

Lost Customers = IF(
HASONEVALUE( insertBK[dateins] ) ,
COUNTROWS ( FILTER ( CALCULATETABLE ( VALUES( adDetails[TrueCompany]),
FILTER( ALL(insertBK[dateins]), insertBK[dateins] =
DATEADD( VALUES( insertBK[dateins] ), -1, MONTH )
), ALL(insertBK[dateins])
) ,
adDetails[TrueCompany]<>
CALCULATETABLE( VALUES ( adDetails[TrueCompany] ) )
)
))

To be honest I am njot sure what it is returning.  I have changed the tables in the code to where I think it should be pointing.

 

I am trying to understand what is going on and why it is not working, would someone be able to explain to me.

 

Thanks

 

Chris

2 ACCEPTED SOLUTIONS

Hi @Anonymous,

 

If your sales table only contain the consumption records, you can filter the records by date(for example 2015) then get the customer column from the filtered table and use EXCEPT Function to filter data with customer table.

 

If above is not help, can you share some sample data?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

CheenuSing
Community Champion
Community Champion

Hi @Anonymous

 

Follow the link

 

New and Returning Customers

Repeat Customers in DAX: Three Flavors

 

They are excellent postings on this topic which clearly explain the logic behind doing the calculations.

 

If this helps please give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
CheenuSing
Community Champion
Community Champion

Hi @Anonymous

 

Follow the link

 

New and Returning Customers

Repeat Customers in DAX: Three Flavors

 

They are excellent postings on this topic which clearly explain the logic behind doing the calculations.

 

If this helps please give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

Thanks guys, will lookk into that see how I get on.

Anonymous
Not applicable

Or something like

 

Lost Customers = CALCULATE(sum(insertBK[InsertNetCost]),'Calendar$'[Year]=2015,var x = CALCULATE(sum(insertBK[InsertNetCost]),'Calendar$'[Year]=2015) = null)

 

which doesnt work

Hi @Anonymous,

 

If your sales table only contain the consumption records, you can filter the records by date(for example 2015) then get the customer column from the filtered table and use EXCEPT Function to filter data with customer table.

 

If above is not help, can you share some sample data?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.