Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Hi @Anonymous
Follow the link
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
Hi @Anonymous
Follow the link
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
Thanks guys, will lookk into that see how I get on.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |