Forum Discussion

Junaid11's avatar
Junaid11
Icon for Helper V rankHelper V
3 years ago

Compare text value with previous month

Hello,
I want to compare Code which is Text with previous month. Each code can come is unique like in January this code can only comes one not more than that. Same code can come in next month but it can only come only once there as well. 
My tale looks like below:

MonthCode
March112
March123
April866
April112
May656
May234

I want to create a new column and comapre these codes with previous month. If For example code 112 in April is also in previous month then the value against it should be like unchanged. If the codeis not in previous month then it should show new else it should show gone. I am attaching a outcome table as well along with the criteria.

MonthCodeOutcome
March112GONE
March123GONE
April866NEW
April112UNCHANGED
May656NEW
May234NEW

If Exist in March and April: 'Unchanged'
If Exist in March Not Exist April: 'Gone'
if don't exist in march and exist in April: 'New"
It would be highly appreciated if I can get the response.
Thank you

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Junaid11 , Try like

     

    New column =
    var _date = [Date]
    var _code = maxx(filter(Table,eomonth(_date,0) = eomonth([Date],0) ),[Code])
    return
    Switch(True(),

    [Code] = _code, "Unchanged",

    Not(isblank(_code)) && [Code]<> _code, "New" , "Gone")

    • Junaid11's avatar
      Junaid11
      Icon for Helper V rankHelper V
      3 years ago

      Hello amitchandak ,

      The code is not working I am getting below outcome which is not similar to outocme I have mentioned.