Purchase Order Filter: New, Existing and Idle

[na12063]

I need to create filter as "New", Existing" and "Idle" for purchase orders.

Lets say I have a table :

| Name  |   OrderDate         |    FY       | OrderType   |

    A            11-May-2018        2018          NEW

    B             25-Jun- 2018        2018        EXISTING 

    B             5-Sep-2018          2018         EXISTING 

    B              9-Oct -2017         2017        EXISTING

    C             1-Jan-2017           2016           IDLE

    C             2-Jan-2018           2018           IDLE 

IDLE should be when we do not have a PO(Purchase Order) for one year

Any help will be appriciated.  

[v-danhe-msft]

Hi @na12063,

Could you mean to create the column like [Order Type] in your table? If so, could you please offer me more information about how to judge "NEW" and "EXISTING"? If not, could you please offer me more information and post your desired result if possible?

Regards,

Daniel He

[na12063]

OrderType = 
var A = calculate(SUM('Table'[Qty]), Filter(ALLEXCEPT('Table', 'Table'[Item]),  YEAR('Table'[Date])=YEAR(TODAY())))
var B = calculate(SUM('Table'[Qty]), Filter(ALLEXCEPT('Table', 'Table'[Item]),  YEAR('Table'[Date])=YEAR(TODAY())-1))
return
   IF (AND(A  = 0,  B >  0), "IDLE",
    IF(AND(A <> 0,  B <> 0), "EXISTING",
    IF(AND(A <> 0,  B =  0),  "NEW", BLANK()
       )
    )
)

[na12063]

Dear Daniel He,

Correct, need to create [OrderType].

NEW should be when the order is only ordered once and never before or after.

EXISTING should be when order is ordered multiple time before or after within every YEAR.

IDLE if it's possible let's say if I have a purchase order on 2016 last time and I do not have same PO within a year ordered that's IDLE. One year peariod between First Time(PO) and (Last TIme)PO. 

Please let me know if you need more information 

Thanks