Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
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() ) ) )
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |