Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
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:
Best Regards,
Lin
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:
Best Regards,
Lin
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,
You may run into the error that the day has not the correct value (eg. your original date is on day 31 and the +x months results in february) you can change the last part to
Dateaddyear = DATE( YEAR( ACKStudies[Factuurdatum] ) + 2 ; MONTH( ACKStudies[Factuurdatum] ) ; 1 + DAY( ACKStudies[Factuurdatum] ) )
In case anyone runs into this error, I just ran into it and couldn't figure it out for a good 20 min so hope it helps someone
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |