Forum Discussion

sestes's avatar
sestes
Regular Visitor
7 years ago
Solved

Yes/No Calculated Column for Last Month

I have the following code to create a calculated column returning "Yes" if the selected date was last month and "No" for everything else.  This was working fine until this month due to the year change.  I know what the problem is, but I'm not sure how to correct it.

 

Here is the custom column code;

 

if (Date.Year([RequestReceived]) = Date.Year(DateTime.LocalNow()) and Date.Month([RequestReceived]) = Date.Month(DateTime.LocalNow())-1) then "Yes" else "No"

 

It's clear that the problem is that the code is looking to see if the year matches then looking to see if the month is the current month minus one.  This was needed so the formula was taking into account both year and month.  Of course, last month's year will not match, so I'm not getting the correct result from the formula.  I expect that in February this code will work fine again, but every January it will not.  How can I revise this, or better calculate if the date in the selected field was last month?

 

Thanks,

Shawn

  • sestes

     

    Hi, In Pquery yo can create a Custom Column with this code.

     

    =if Date.IsInPreviousNMonths([Request_Recieved], 1)=true then "Yes" else "No"

     

    Regards

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    Seems like you could nest your if statements such that you first check if the current month is 1 and if so then see if the month for the date is 12 and if so Yes, otherwise, your current calculation.

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion

    sestes

     

    Hi, In Pquery yo can create a Custom Column with this code.

     

    =if Date.IsInPreviousNMonths([Request_Recieved], 1)=true then "Yes" else "No"

     

    Regards

     

  • sestes's avatar
    sestes
    Regular Visitor

    Thank you both for the quick reply.  Vvelarde This was exactly what I needed and works like a charm.