Forum Discussion

kimloh_p's avatar
kimloh_p
Frequent Visitor
6 years ago
Solved

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 logi...
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
kimloh_p
Frequent Visitor
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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