Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Advocate I
Advocate I

Find a Company's last purchase in a list of ALL Purchases from ALL Companies

I have a table called "Order Summary" with 10k+ rows and the following columns:

order_ID      date      account_number      previous_order_ID      previous_order_date      time_elapsed      
data   data   data   ?????????

(the table has an addition 29 columns, like company information, qty, payments, etc.  They are not relevant to this question, but it should be noted that they are there)


The first 3 columns make up my dataset already - It's the last 3 columns I'm trying to fill in.  That is to say:

for each individual row of unique order_ID, I want to know

  1. The [order_ID] for the last purchase that was made by the specific company.
  2. The date that the [previous_order_ID] was made
  3. The time elapsed between [date] and [previous_order_date]

My first hurdle is identifying the MAX [order_ID], but limiting it to the specific company in the row AND ensuring that it is less than the [order_ID] in the specific row.



Advocate I
Advocate I

Thanks!  I appreciate your time.  I had a look, but it wasn't in the direction I was trying to go in.  I tried rewording the question in a different way and found some code that worked.  

If anyone is looking in the future, try here:

Quicker Way to do "MAXIFS" to Find the Last Purchase Date 

Super User
Super User

Super User
Super User

I think you can use this tutorial by Radacad to creare a group by per ID. You could then add a MAX row in the group by step to get the max value of the column

@ChielFaber Which tutorial are you referring to (I don't see a link)....

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors