Forum Discussion
DAX lookupvalue
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")
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")