Forum Discussion
Filtering with DatesInPeriod and Max
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.
- https://community.powerbi.com/t5/Desktop/Issue-with-using-MAX-as-a-filter-for-date-in-CALCULATE-DAX/td-p/123019
- Where Excelside went into detail about why max in a filter causes context transition.
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))
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
- Customer table – distinct customers
- Sales table – distinct sales, by customer, by date
- Dates table – distinct date
The relationship
- Sales has a many to one between the customers table and dates table.
- Many sales to one customer
- Many sales to one date
3 Replies
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. :smileyhappy:
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
- Customer table – distinct customers
- Sales table – distinct sales, by customer, by date
- Dates table – distinct date
The relationship
- Sales has a many to one between the customers table and dates table.
- Many sales to one customer
- Many sales to one date
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). :smileyhappy:
Regards
