addmonth in where clause

[aheavy95]

Hi all 
I am trying to load in a loop the closing inventory of the previous month each time.

The  "Sale_Year_Month" field is formatted '01/07/2023' (The alias  is confusing )

My problem is in the where clause. The previous one which is greyed, worked fine (where Sale_Year_Month='$(YM)'-1)

But as I am trying to use the addmonth function so it works also when the year changes- the where clause Sale_Year_Month=AddMonths('$(YM)',-1)  isn't working and I'm getting an empty table. I guess I am missing some syntax.

Thanks

[Or]

Best guess would be that Sale_Year_Month isn't picked up as a date. Have you tried explicitly converting it to a date using date#()?

[aheavy95]

I loaded the script without the where clause:

 the Sale_Year_Month is picked up as a date, but I inserted the-  addmonths('$(YM)',1) as Sale_Year_MonthNew and it seems like the problem occurs with that expression

[AshutoshBhumkar · Accepted solution]

Hi,

Check the YM variable.

It is returning the 7.06 value. It should return some Date right ?

Please correct me if I understood this in another way.

Thanks,
Ashutosh