cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RemiAnthonise
Helper V
Helper V

Calculated column with DateAdd

Hi guys,

 

I have a date-table with a column called 'date', so 'date'[date].

In my other table I have a column called Transdate, 'Studies'[Transdate]. I want to add some months at this table with DateAdd. 

There is a 1:n relationship between Date and Transdate. 

My' Studies'[Transdate] doesn't include the dates that I want to add / use so I need to refer to the 'date'[date]. How do I do this?

 

See sample data

https://www.dropbox.com/s/zk6w6upch8n9j2p/example.pbix?dl=0 

2 ACCEPTED SOLUTIONS

Hi, 

 

Like Dateadd doen't work for you, if you want to add 2 years to Factuurdatum, try this formula: 

Dateaddyear = 
DATE( YEAR( ACKStudies[Factuurdatum] ) + 2 ; MONTH( ACKStudies[Factuurdatum] ) ; DAY( ACKStudies[Factuurdatum] ) )

You also think to do it in M instead.

 

Regards,

View solution in original post

hi, @RemiAnthonise

You can combine your formula with luxpbi's formula as below:

Dateadd = DATE( YEAR( ACKStudies[Factuurdatum] ) + (1-WEEKDAY(ACKStudies[Factuurdatum],2)) , MONTH( ACKStudies[Factuurdatum] ) , DAY( ACKStudies[Factuurdatum] ) )

Result:

1.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
luxpbi
Helper V
Helper V

Hi @RemiAnthonise,

 

From what date you want to extract the month?

Regards,

Hi @luxpbi

 

For now I want to add 2 or 4 years to the TransDate. But I see I didn't translate my .pbix from Dutch to English. I'm sorry. In my previous post I've mentioned Transdate, in my file this is called factuurdatum. 

 

Like I said, I want to add 2 or 4 years to this date. This will depend on another field so I'll create an If. This won't be a problem, I assume, but I need to get the DateAdd working first.

hi, @RemiAnthonise

You can combine your formula with luxpbi's formula as below:

Dateadd = DATE( YEAR( ACKStudies[Factuurdatum] ) + (1-WEEKDAY(ACKStudies[Factuurdatum],2)) , MONTH( ACKStudies[Factuurdatum] ) , DAY( ACKStudies[Factuurdatum] ) )

Result:

1.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi, 

 

Like Dateadd doen't work for you, if you want to add 2 years to Factuurdatum, try this formula: 

Dateaddyear = 
DATE( YEAR( ACKStudies[Factuurdatum] ) + 2 ; MONTH( ACKStudies[Factuurdatum] ) ; DAY( ACKStudies[Factuurdatum] ) )

You also think to do it in M instead.

 

Regards,

View solution in original post

You can always add X years to a year number, but you can't do that with MM or DD (what's the 13th month ?).  So the question remains: how do you create a calculated-column that is 1month later than "myDate" column ?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.