Date Column based on selected period

[M_SBS_6]

Hi, 

I have a date column app_date of which I need to create my next column from. 

What I'm trying to do in this date column is create a rolling date from today's date. If today, I want to have a rolling 14 days and to call this "rolling current year".

In the same column, I want to the show the same period but 1 year ago and for this to be called "rolling previous year". 

So if we take app_date = today then show "rolling current year" back to 13 days prior (20th October) to today. Then the same principle for last year. If it falls either side of these 14 days, then blank. 

Any idea how I can do this please? 

[ryan_mayu]

could you pls provide some sample data and expected output?

[M_SBS_6]

App_date.       New_date_flag

02/11/2023.    Rolling current year

01/11/2023.    Rolling current year

........

20/10/2023.    Rolling current year

19/10/2023.     (Blank) 

18/10/2023.     (Blank)

Then the following day it changes and moves forward one day so the 20/10/2023 will show as (blank).

The same principle for the same date period 1 year ago. I hope that makes sense. 

[ryan_mayu · Accepted solution]

@M_SBS_6 

maybe you can try this

New_date_flag = 
if(today()-'Table'[App_date]<=14,"Rolling current year",if(EDATE(today(),-12)-'Table'[App_date]<14&&EDATE(today(),-12)-'Table'[App_date]>=0 && year('Table'[App_date])=year(today())-1,"rolling previus year")
)

pls see the attachment below

[M_SBS_6]

Thank you so much for your help 

[ryan_mayu]

you are welcome