Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help with getting the next 9 month date

Hi,

 

I have a date field and I need to identify the next multiple of 9 months since that date and it has to be after the current date, so for example:

 

Date = 9th March 2018

 

I need the next multiple of 9 months after this date after today, so:

 

it would be 9th Sept 2019. The first multiple of 9 months after the original date would be 9th Dec 2018 which is before today's date, so I have to add another 9 months until the date is after today.

 

My dates go back to 2002.

 

I have no idea where to start on this one. Has anyone got any ideas or has come across something similar at all?

 

Many thanks,

Andy

 

 

 

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous 

Assuming you have the date in a column Date in Table1, create a calculated column:

 

NewCol =
VAR Jumps_ =
    QUOTIENT ( DATEDIFF ( Table1[Date], TODAY (), MONTH ), 9 )
RETURN
    IF (
        EDATE ( Table1[Date], Jumps_ * 9 ) = TODAY (), //Check whether today is exactly 9N months away  
        TODAY (),
        EDATE ( Table1[Date], ( Jumps_ + 1 ) * 9 )
    )

 

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @Anonymous 

Assuming you have the date in a column Date in Table1, create a calculated column:

 

NewCol =
VAR Jumps_ =
    QUOTIENT ( DATEDIFF ( Table1[Date], TODAY (), MONTH ), 9 )
RETURN
    IF (
        EDATE ( Table1[Date], Jumps_ * 9 ) = TODAY (), //Check whether today is exactly 9N months away  
        TODAY (),
        EDATE ( Table1[Date], ( Jumps_ + 1 ) * 9 )
    )

 

Anonymous
Not applicable

Thanks @AlB , although I didn't use your exact solution (there were other issues as to why), part of it gave me an idea and I used some of it as part of the solution to get the result I needed. Thank you so much for replying and giving me that point in the right direction.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.