Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gogrizz
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.

 

Thanks!

4 REPLIES 4
gogrizz
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 

ChielFaber
Super User
Super User

ChielFaber
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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors