Find previous manufacturer

[selpaqm]

Hi,

having a table as summarized below. 

Supplier	item	shipment date	Unique	Desired Result
aaaa	11111	01/08/2023	aaaa-11111	First Supplier
bbbb	22222	03/08/2023	bbbb-22222	First Supplier
cccc	11111	03/08/2023	cccc-11111	aaaa (Note: because aaaa produced 1111 at 01/08
dddd	33333	04/08/2023	dddd-33333	First Supplier
eeee	22222	05/08/2023	eeee-22222	bbbb
fffff	11111	06/08/2023	fffff-11111	cccc (first supplier is aaaa but previous supplier is ccccc)
ggggg	33333	07/08/2023	ggggg-33333	dddd
cccc	11111	03/08/2023	cccc-11111	aaaa
cccc	11111	05/08/2023	cccc-11111	aaaa
cccc	11111	02/08/2023	cccc-11111	aaaa

in the table, I added a calculated column to find if the supplier for the item is changed. it adds yes or no. however, my manager want to see previous supplier as well as new supplier and I could not able to add it. 

[Ahmedx · Accepted solution]

Is this what you are looking for?

Column = 
VAR _MinDate = CALCULATE(MIN('Table'[shipment date]),ALLEXCEPT('Table','Table'[item]))
VAR _items = CALCULATE(MIN('Table'[upplier]),ALLEXCEPT('Table','Table'[item]),'Table'[shipment date]=_MinDate)

RETURN
IF(_items =[upplier],"First Supplier",_items)

[Semaj06]

Previous supplier =

VAR datepivot = Feuil1[shipment date]

var result=

    IF (

        CALCULATE (

            COUNTROWS ( Feuil1 ),

            Feuil1[shipment date] < datepivot,

            ALLEXCEPT ( Feuil1, Feuil1[item] )

        ) = 0,

        "First supplier",

        CALCULATE (

            MAX ( Feuil1[Supplier] ),

            Feuil1[shipment date] <EARLIER(Feuil1[shipment date]),

            ALLEXCEPT ( Feuil1, Feuil1[item] )

        )

    )

return

result

[Semaj06]

HI Try this

Previous supplier =
VAR datepivot = Feuil1[shipment date]
RETURN
IF (
CALCULATE (
COUNTROWS ( Feuil1 ),
Feuil1[shipment date] < datepivot,
ALLEXCEPT ( Feuil1, Feuil1[item] )
) = 0,
"First send",
CALCULATE (
MAX ( Feuil1[Supplier] ),
Feuil1[shipment date] < datepivot,
ALLEXCEPT ( Feuil1, Feuil1[item] )
)
)

[selpaqm]

Hi @Semaj06 ,

thanks for your respond however it is not fit all of the data in the column is first send in my column.

[Ritaf1983]

Hi @selpaqm 
What do you mean by the previous supplier?
Can you show what is the desired result?

[selpaqm]

Hi @Ritaf1983 ,

thanks for your respond. I have amended table. can you please check and advise?