Calculated column to capture next three months dates from today and past dates from a date column

[Anonymous]

Hi all,

I have a column with dates in the following format: dd/mm/yyyy (sample data below)

How can I create a calculated column that only captures any date in next three months from today and all past dates. any blanks can be ignored. I have tried using EOMONTH but it captures from end of month to next three months but i want from today.

Any help will be highly appreciated 🙂

date in past or next 3 months =

VAR _Nextthreemonth = EOMONTH ( TODAY(), +3 )

RETURN

IF(

ISBLANK(table[ date]),

BLANK(),

IF ( [date] <= _Nextthreemonth, "Yes","No")

)

Thank you in advance.

Date column

16/12/2021

17/12/2021

18/12/2021

21/12/2021

22/12/2021

13/01/2022

14/01/2022

15/01/2022

26/01/2022

23/02/2022

24/02/2022

25/02/2022

01/03/2022

18/03/2022

22/03/2022

24/03/2022

25/03/2022

26/03/2022

28/03/2022

29/03/2022

30/03/2022

31/03/2022

01/05/2022

02/06/2022

04/06/2022

05/08/2022

06/07/2022

07/09/2022

08/11/2022

09/12/2022

[amitchandak]

@Anonymous , Try like

date in past or next 3 months =
VAR _Nextthreemonth = EOMONTH ( TODAY(), +3 )
RETURN

IF(
ISBLANK(table[ date]),
BLANK(),

IF ( [date] <= _Nextthreemonth && [date] >=today(), "Yes","No")
)

[Anonymous]

Hi @amitchandak ,

Thank you very much for your prompt and helpful response. 

However, it is capturing till end of July but i was hoping to capture ONLY three months from today which would be 7th or 8th July. is there any other option than EOMONTH ?

Thank you again

[Anonymous · Accepted solution]

HI @Anonymous,

You can try to use date function to get the the end date for calculate expression usages:

next 3 months =
VAR _threeMonthAfterToday =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, DAY ( TODAY () ) )
RETURN
    IF (
        ISBLANK ( table[ date] ),
        BLANK (),
        IF ( [date] <= _threeMonthAfterToday && [date] >= TODAY (), "Yes", "No" )
    )

Regards,

Xiaoxin Sheng