Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a list of purchase orders for all of our customers. I would like to know how to get the previous purchase order for a customer.
Please keep in mind the numbers of purchase orders are randomly generated.
I very much appreaciate your help.
BR
Solved! Go to Solution.
Or this Calculated Column
Column = VAR myLastDate = CALCULATE ( MAX ( 'Table1'[Purchase Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Client] ), 'Table1'[Purchase Date] < EARLIER ( 'Table1'[Purchase Date] ) ) ) RETURN CALCULATE ( FIRSTNONBLANK ( 'Table1'[Purchase Order], 1 ), FILTER ( ALLEXCEPT ( Table1, Table1[Client] ), 'Table1'[Purchase Date] = myLastDate ) )
Hi @kaledjeff
This calculated column is close
Previous Order (Desired Result) = VAR myLastDate = CALCULATE( MAX('Table1'[Purchase Date]), FILTER( 'Table1','Table1'[Client] = EARLIER('Table1'[Client]) && 'Table1'[Purchase Date] < EARLIER('Table1'[Purchase Date]) ) ) RETURN CALCULATE( MAX('Table1'[Purchase Order]), FILTER('Table1','Table1'[Purchase Date] = myLastDate) )
Thank you @Phil_Seamark @Zubair_Muhammad for help.
I tested the calculated column by @Zubair_Muhammad and it worked like a charm.
@Phil_Seamark, I will test the function and let you know.
I appreacite your help.
BR
HI @kaledjeff
They are essentially the same code, except you don't actually need to use the ALLEXCEPT function. It doesn't do anything in calculated columns - so could just slow down the calculation. 🙂
Hi @kaledjeff
This doesn't grab the MAX purchase order number. It uses the MAX function to find the newest of all purchase orders in the past. It should give you what you need. 🙂
I agree with you !
Your formula works while the one of @Zubair_Muhammad give me an error message : not enough memory to proceed.
Your formula helps me a lot but I steel have an issue.
It sum all "previous order" if there have been several order the same day.
Is there a way to retrieve only the last amount without summing all of them ?
I try to integrate LASTNONBLANK in your formula without success.
Any idea will be appreciated 🙂
Have a nice day
Nicolas
Thank you very much for quick reply.
But it isn't exactly what I need. The purchase number is created randomly. I can't take the max.
BR
I think @Phil_Seamark column should work.
Nevertheless try the following as well
First a MEASURE
Measure = VAR myLastDate = CALCULATE ( MAX ( 'Table1'[Purchase Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Client] ), 'Table1'[Purchase Date] < SELECTEDVALUE ( 'Table1'[Purchase Date] ) ) ) RETURN CALCULATE ( FIRSTNONBLANK ( 'Table1'[Purchase Order], 1 ), FILTER ( ALLEXCEPT ( Table1, Table1[Client] ), 'Table1'[Purchase Date] = myLastDate ) )
Or this Calculated Column
Column = VAR myLastDate = CALCULATE ( MAX ( 'Table1'[Purchase Date] ), FILTER ( ALLEXCEPT ( Table1, Table1[Client] ), 'Table1'[Purchase Date] < EARLIER ( 'Table1'[Purchase Date] ) ) ) RETURN CALCULATE ( FIRSTNONBLANK ( 'Table1'[Purchase Order], 1 ), FILTER ( ALLEXCEPT ( Table1, Table1[Client] ), 'Table1'[Purchase Date] = myLastDate ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |