topic Re: Dax to calculate customers based on there performance last year in DAX Commands and Tips

Dax to calculate customers based on there performance last year

Anonymous — Wed, 30 Jul 2025 11:30:03 GMT

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))

Re: Dax to calculate customers based on there performance last year

bhanu_gautam — Wed, 30 Jul 2025 11:47:50 GMT

Anonymous 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)

Re: Dax to calculate customers based on there performance last year

johnt75 — Wed, 30 Jul 2025 12:21:55 GMT

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

Re: Dax to calculate customers based on there performance last year

FBergamaschi — Wed, 30 Jul 2025 18:21:06 GMT

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

Re: Dax to calculate customers based on there performance last year

Aburar_123 — Thu, 31 Jul 2025 03:12:00 GMT

Hi Anonymous ,

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

Re: Dax to calculate customers based on there performance last year

Anonymous — Thu, 31 Jul 2025 10:11:24 GMT

Hi Anonymous,
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.

Re: Dax to calculate customers based on there performance last year

Anonymous — Sun, 03 Aug 2025 12:03:53 GMT

Hi Anonymous,
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.

Re: Dax to calculate customers based on there performance last year

Anonymous — Mon, 04 Aug 2025 02:42:38 GMT

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.

Re: Dax to calculate customers based on there performance last year

Anonymous — Mon, 04 Aug 2025 02:44:06 GMT

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

Re: Dax to calculate customers based on there performance last year

Anonymous — Mon, 04 Aug 2025 02:49:31 GMT

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.