Filtering with DatesInPeriod and Max

[PieInDaFace]

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/...
  • 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))

[v-ljerr-msft]

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

[PieInDaFace · Accepted solution]

@v-ljerr-msft,

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

[v-ljerr-msft]

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