Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
2 years ago
Solved

Date Column based on selected period

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? 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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

5 Replies

    • M_SBS_6's avatar
      M_SBS_6
      Icon for Helper V rankHelper V
      2 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        2 years ago

        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