Forum Discussion

RemiAnthonise's avatar
RemiAnthonise
Helper V
7 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • luxpbi's avatar
    luxpbi
    7 years ago

    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,

  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

RemiAnthonise's avatar
RemiAnthonise
Helper V
7 years ago

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.

luxpbi's avatar
luxpbi
Helper V
7 years ago

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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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 ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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