Forum Discussion

joansinho's avatar
joansinho
Advocate I
7 years ago
Solved

Current Month Comparison

Hi everyone,
 
I need some help with a calculation. I need to create a new column that shows if an application has been returned the "Current Month." If the application has been returned the current month, it should be "YES." If an application has been returned in past months it should be "RESET," and if it has not been returned it should be "NO."
Example of what I need:
image.png
 
I am trying to use this formula, but I am not getting the result I need
 
Formula: 
 
Aplication Returned = If(Contact[Most Recent Aplication return]= BLANK(), "NO", IF(Contact[Most Recent Aplication return] = date(year(today()),month(today())-1,1), "YES", "RESET"))
 
The result I get: 
 
image.png
 
It must be something simple I am missing, but I can't seem to figure out.
 
Thank you in advance.
Need Help
  • jthomson's avatar
    jthomson
    7 years ago

    Think your IF statement is using incorrect logic, by the looks of thing it's only going to return yes if your return date is exactly the first of the previous month. Why not try something like:

     

    Returned = var todaysdate = today()

    if ([recentreturn] = blank(), "no", if (month([recentreturn])=month(todaysdate)&&(year([recentreturn])=year(todaysdate),"yes","reset")

     

    i.e. compare the year and the month of your column to the year and the month of today and see if they both match

4 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    7 years ago

    Think your IF statement is using incorrect logic, by the looks of thing it's only going to return yes if your return date is exactly the first of the previous month. Why not try something like:

     

    Returned = var todaysdate = today()

    if ([recentreturn] = blank(), "no", if (month([recentreturn])=month(todaysdate)&&(year([recentreturn])=year(todaysdate),"yes","reset")

     

    i.e. compare the year and the month of your column to the year and the month of today and see if they both match

    • joansinho's avatar
      joansinho
      Advocate I
      7 years ago

      What I need!

       

      v-lili6-msft

       

      Thank you! I am uploading new pictures.

       

      What I get with the formula I am using