Forum Discussion

selpaqm's avatar
selpaqm
Icon for Helper V rankHelper V
3 years ago
Solved

Find previous manufacturer

Hi,

 

having a table as summarized below. 

Supplieritemshipment dateUniqueDesired Result
aaaa1111101/08/2023aaaa-11111First Supplier
bbbb2222203/08/2023bbbb-22222First Supplier
cccc1111103/08/2023cccc-11111aaaa (Note: because aaaa produced 1111 at 01/08
dddd3333304/08/2023dddd-33333First Supplier
eeee2222205/08/2023eeee-22222bbbb
fffff1111106/08/2023fffff-11111cccc (first supplier is aaaa but previous supplier is ccccc)
ggggg3333307/08/2023ggggg-33333dddd
cccc1111103/08/2023cccc-11111aaaa
cccc1111105/08/2023cccc-11111aaaa
cccc1111102/08/2023cccc-11111aaaa

 

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. 

Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    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)

     

     

6 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    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)

     

     

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

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

    • selpaqm's avatar
      selpaqm
      Icon for Helper V rankHelper V
      3 years ago

      Hi Ritaf1983 ,

       

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

  • Semaj06's avatar
    Semaj06
    Regular Visitor
    3 years ago

    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's avatar
      selpaqm
      Icon for Helper V rankHelper V
      3 years ago

      Hi Semaj06 ,

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

       

  • Semaj06's avatar
    Semaj06
    Regular Visitor
    3 years ago
    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