Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Howdy all,
I’ve been trying to get customers previous 3 sales for a while, and below is what I came up with, but man is it slow. Researching more, it seems due to using a max in the filter that causes the sluggish calculation.
I’m hoping somebody can point me in the right direction. I’d hate to use sql to resolve the issue…..
Sales:=SUM([gross sales])
Previous three Sales :=CALCULATE([Sales], DATESINPERIOD(‘Dates'[TimeDate],LASTDATE('Dates'[TimeDate]),-13,MONTH), Customers[Territory_Group] = "OOT", FILTER( ALLEXCEPT('Customer History', 'Customers'[CustomerID]), 'Customer History'[TripNumber] > MAX('Customer History'[TripNumber] ) - 3))
Solved! Go to Solution.
Thanks for the reply. Turns out I was being a dunce and completely forgot about how to use the TopN function....
Below is what I came up with for a faster measurement.
Max Trip Number:= MAX([TripNumber])
Previous three Sales:=CALCULATE([Sales], Customers[Territory_Group] = "OOT", TOPN(3,DATESINPERIOD(‘Dates'[TimeDate],LASTDATE('Dates'[TimeDate]),-13,MONTH),[Max Trip Number],DESC ))
For the table structure,
Three tables
The relationship
Hi @PieInDaFace,
Could you post your table structures(including the relationships) with some sample/mock data which can help us to reproduce the issue, so that we can better assist on it? It's better that you can share a sample pbix file(with just some sample/mock data) which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
Thanks for the reply. Turns out I was being a dunce and completely forgot about how to use the TopN function....
Below is what I came up with for a faster measurement.
Max Trip Number:= MAX([TripNumber])
Previous three Sales:=CALCULATE([Sales], Customers[Territory_Group] = "OOT", TOPN(3,DATESINPERIOD(‘Dates'[TimeDate],LASTDATE('Dates'[TimeDate]),-13,MONTH),[Max Trip Number],DESC ))
For the table structure,
Three tables
The relationship
Hi @PieInDaFace,
Brilliant! Could you accept your reply above as solution to close this thread(it will also help others who have similar issue easily find the answer).
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |