Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DATEADD

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]...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous,

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's avatar
    PMF99
    Regular Visitor
    7 years ago

    Thanks NickNg.

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 )