Forum Discussion
DAX lookupvalue
Hi Community,
I have a single table that contains the following columns [Date], [ID], [Product].
I would like to add [Removed] and [New] calculated columns with the results below and as per logic:
if ID is not found in previous month, mark product as New.
if ID is not found in next month, mark product as Removed.
I'm still new and struggling with using DAX for this, appreciate some help. Thank you.
| Date | ID | Product | Removed | New |
| 1/1/2019 | 12345 | Policy_A | Y | Y |
| 1/1/2019 | 23456 | Policy_B | Y | |
| 1/1/2019 | 34567 | Policy_C | Y | |
| 2/1/2019 | 45678 | Policy_D | Y | |
| 2/1/2019 | 23456 | Policy_B | ||
| 2/1/2019 | 34567 | Policy_C | Y | |
| 3/1/2019 | 45678 | Policy_D | Y | |
| 3/1/2019 | 23456 | Policy_B | Y | |
| 3/1/2019 | 56789 | Policy_E | Y | |
| 4/1/2019 | 67890 | Policy_F | Y | |
| 4/1/2019 | 56789 | Policy_E |
kimloh_p Not sure about granularity of the data but you can use the max date condition in if condition.
Removed_ = VAR _nextMonth = MONTH(NEXTMONTH('Table'[Date])) VAR _maxmonth = MONTH(MAX('Table'[Date])) VAR _table = CALCULATETABLE(VALUES('Table'[ID]),FILTER(ALL('Table'),'Table'[Date].[MonthNo]=_nextMonth)) RETURN IF('Table'[ID] IN _table||'Table'[Date]=MAX('Table'[Date]),BLANK(),"Y")
3 Replies
kimloh_p Please use below calculated column for new and removed
New = VAR _prevMonth = MONTH(PREVIOUSMONTH('Table'[Date])) VAR _table = CALCULATETABLE(VALUES('Table'[ID]),FILTER(ALL('Table'),'Table'[Date].[MonthNo]=_prevMonth)) RETURN IF('Table'[ID] IN _table,BLANK(),"Y") Removed = VAR _nextMonth = MONTH(NEXTMONTH('Table'[Date])) VAR _table = CALCULATETABLE(VALUES('Table'[ID]),FILTER(ALL('Table'),'Table'[Date].[MonthNo]=_nextMonth)) RETURN IF('Table'[ID] IN _table,BLANK(),"Y")You might want to modify removed definition as per your need. Currently I'm assuming that by default product is considered as removed for latest month. Please let me know if you have any question. If it help accept solution.
hi Anonymous
Thank you for the solution, it's so well written, i wouldn't have figured out this method alone.
As for products in the latest month, it should not reflect in the [Removed] column if there is no match in the nextMonth. How do I incorporate this exeptional condition?
Thank you in advance.
kimloh_p Not sure about granularity of the data but you can use the max date condition in if condition.
Removed_ = VAR _nextMonth = MONTH(NEXTMONTH('Table'[Date])) VAR _maxmonth = MONTH(MAX('Table'[Date])) VAR _table = CALCULATETABLE(VALUES('Table'[ID]),FILTER(ALL('Table'),'Table'[Date].[MonthNo]=_nextMonth)) RETURN IF('Table'[ID] IN _table||'Table'[Date]=MAX('Table'[Date]),BLANK(),"Y")
