IF function with date greater / smaller 10 (month)

[Uygar]

Hi,

I have created a formula which is referring to a column with the month from 1 to 12.

So it filters the months which are smaller 9.

That works perfectly but when I enter 10, 11 or 12 the formula

does not work anymore.

Seems like it only counts from 1 to 9 and does not recognize numbers that are greater?

Is this due to the format?

Thank you very much for your help.

[v-jayw-msft · Accepted solution]

Hi @Uygar ,

Size comparison logic between text types are different between and number types.

Please use numeric type instead.

You could use below formula to create numeric yearmonth column.

yearmonth = year([date])*100+month([date])

[Uygar]

I can share the formula that works:

IF('Sales'[Month and Year]<"8 - 2022", round(LOOKUPVALUE('currency'[rate per EUR], 'currency'[Date], 'Sales'[Month and Year])*'Sales'[Amount],2),

round(LOOKUPVALUE('currency'[rate per EUR], 'currency'[Type and Date], "Planning 2023")*'Sales'[Amount],2)

So this works, but when I enter <"10 - 2022" instead of <"8 - 2022" it does not work anymore, does not show the correct amount for the month.

Thank you

[Uygar]

somehow it does not recognize it when the filter is greater "9 - 2022"..

[NikhilChenna]

Hi @Uygar ,

Can you please share a sample data set with the formula it will be easy to understand.

Regards,

Nikhil Chenna

[FreemanZ]

can you paste your formula and also some sample data?