DAX lookupvalue

[kimloh_p]

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

[Anonymous]

@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.

[kimloh_p]

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.

[Anonymous · Accepted solution]

@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")