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],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

 

 

 

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 )

3 Replies

  • 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 )