Forum Discussion

PieInDaFace's avatar
PieInDaFace
Frequent Visitor
8 years ago
Solved

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 c...
  • PieInDaFace's avatar
    PieInDaFace
    8 years ago

    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's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

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

PieInDaFace's avatar
PieInDaFace
Frequent Visitor
8 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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