DATEADD

[PMF999]

Hi All,

I have this table

I'm trying to reduce Date1 by the Month column and return the results in Date2.

I'm using the formula;

=dateadd(Resign_Append[Date1],-Resign_Append[Month],MONTH)

I have no idea why I'm getting values in some rows of Date2 and not in others (as above).

Any ideas?

Thanks in advance

[Anonymous]

@PMF999,

DATEADD is a time intelligence function that returns a table, so you will get the incorrect results. You use Date.AddMonths() function in Power Query to substract the month from your date.

[PMF99]

Thanks NickNg.

Any idea how do perform this calculation in DAX as I have multiple queries in my model?

[Anonymous · Accepted solution]

@PMF99 ,

Here is the formula in DAX: 

New Date DAX = 
VAR _year = YEAR ( Resign_Append[Date1] ) 
VAR _month = MONTH ( Resign_Append[Date1] )
VAR _day = DAY ( Resign_Append[Date1] ) 
VAR _monthToSubstract = Resign_Append[Month]
RETURN DATE ( _year, _month - _monthToSubstract, _day )