Solved: Last Month

Dave_Willett · 11-16-2020 03:45 AM

MONTH ([InvoicedDate]) = MONTH (TODAY ())

The above expression gives ma data for the current month based on [InvoicedDate].

What would be the expression to return data from Last month again based on [InvoicedDate] ?

Thanks

Dave_Willett

Thank you Suvrutt
Works a treat and understoof over month-1 would revert to 0.

View solution in original post

Chris_Jeal

MONTH ([InvoicedDate]) = MONTH (TODAY ())-1

Dave_Willett

So easy…
I had tried: MONTH ([InvoicedDate]) = MONTH (TODAY ()-1)

Thank You

Suvrutt_Gurjar

I believe , if you wish the records to be returned for previous month of December ( Month number 12) in the month of January (Month number 1) next year, then the expression needs to be something like

IF(MONTH(TODAY())-1=0, AND(MONTH ([InvoiceDate])=12, YEAR([InvoiceDate])=YEAR(TODAY())-1) , MONTH ([InvoiceDate]) = MONTH (TODAY ())-1)

This is so because I believe in the month of January the expression MONTH (TODAY ()-1) will return 0 , instead of 12

Dave_Willett

Thank you Suvrutt
Works a treat and understoof over month-1 would revert to 0.

Markus_Malessa

I believe @Suvrutt_Gurjar is correct in their assessment. You would want to be careful arbitrarily subtracting from just the month value as it could result in undesired results. Rather use the full date initially. I would suggest possibly exploring the EOMONTH([InvoiceDate], -1) function. I am not sure if the second parameter of the EOMONTH() function will take a negative value but it is worth a try.

Steve

Try:

(EOMONTH([InvoicedDate], 0) = EOMONTH(TODAY(), -1))