Dax to calculate customers based on there performance last year

[amaan91]

Hi Guys 

I am trying to track segment customer based on their perfromance last year. 

logic - List of customers that have spend more than 10k in the last calleder year  then check if they exisit thisyear. 

To do this i was intending to use intersect, but i am unable to calculate the list of cusotmer Last year as a single virtual column. 

this code works fine in Dax studios but is not accurate in a measure 

* it is critical to get only customerlist column as having multiple columns is treated a single entity in Except and intersect function. 

var My_Table =  CALCULATETABLE(
VALUES(Append_Fact[CustomerList_Filtered]) ,   
FILTER( SUMMARIZE( 
FILTER( Append_Fact , Append_Fact[Year]=2024) , 
Append_Fact[CustomerList_Filtered] , Append_Fact[Year]  ,
"Rev" , [SWR] ) ,
[rev]>15000))

[v-hjannapu · Accepted solution]

Hi @amaan91,
I would also take a moment to thank @bhanu_gautam , for actively participating in the community forum and for the solutions you have been sharing in the community forum. Your contributions make a real difference.

You can achieve this by creating two calculated tables  one for customers who spent more than 10k last year and another for customers this year  and then use the INTERSECT function to get the common ones. This will return only the Customer column as needed.

I tested it with my sample data, and it worked fine. Please find the attached screenshot and Pbix for your reference.

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.

Best Regards,
Harshitha.

[v-hjannapu]

Hi @amaan91,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[Aburar_123]

Hi @amaan91 ,

Please try the below approach,

For example you have the data as below:

So, you have to create the below calculated columns,

Prev Year = 'Table'[Year]-1

IsCust_Gt_15000_PY =

var prev_year_amount = CALCULATE(SUM('Table'[Order Amount]),FILTER('Table','Table'[Customer Id]=EARLIER('Table'[Customer Id]) && 'Table'[Year]=EARLIER('Table'[Prev Year])))

return if(prev_year_amount>15000,1,0)

So, your final result would be like below,

you just have to filter the records with IsCust_Gt_15000_PY=1

[FBergamaschi]

Can you please provide a sample of the tables and show in an image the inaccuracy you are complaining about?

[amaan91]

I have used the same code it seems to be working now. 

My original issue was resolved by i think just update the Power BI version, but this code seems to not be eliminate the existing filter context. 

[johnt75]

If you are only interested in the customers who spent 15k+ in 2024, and you don't need to slice by other dimensions like product category, it would probably be more efficient to create a calculated column on the customer dimension, either as a true / false value or showing the actual amount spent, e.g.

'Customer'[Over 15k 2024] =
VAR Revenue =
    CALCULATE ( [SWR], Append_Fact[Year] = 2024 )
VAR Result = Revenue > 15000
RETURN
    Result

[amaan91]

i get that but , the customer demention also has another (store level) dimention to it and this is causing all the caos. 

[bhanu_gautam]

@amaan91 Create a calculated table for customers who spent more than 10k last year:

Customers_Last_Year =
CALCULATETABLE(
VALUES(Append_Fact[CustomerList_Filtered]),
FILTER(
SUMMARIZE(
FILTER(Append_Fact, Append_Fact[Year] = 2023), // Adjust the year as needed
Append_Fact[CustomerList_Filtered],
"Rev", [SWR]
),
[Rev] > 10000
)
)

Create a calculated table for customers this year:

dax
Customers_This_Year =
CALCULATETABLE(
VALUES(Append_Fact[CustomerList_Filtered]),
Append_Fact[Year] = 2024 // Adjust the year as needed
)

Use the INTERSECT function to find common customers:

dax
Common_Customers =
INTERSECT(Customers_Last_Year, Customers_This_Year)

[amaan91]

Thanks @bhanu_gautam  i am alreadly using this logic , but the trouble now is with this code is, it is not ignoring 100% of the filter context in the report even after using Allexcept . 
This code workes gread in the Dax editor but is playing up in the measure in the report tab. 
Need a code to generate the only the list of Customers for previous years who have spent more than 10K( or  x amount) using AllExept condition to all some filter contect to go through.