cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PMF999 Frequent Visitor
Frequent Visitor

DATEADD

Hi All,

 

I have this table

Capture.PNG

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: DATEADD

@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 3
NickNg278 Senior Member
Senior Member

Re: DATEADD

@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 Frequent Visitor
Frequent Visitor

Re: DATEADD

Thanks NickNg.

 

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

NickNg278 Senior Member
Senior Member

Re: DATEADD

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 833 guests
Please welcome our newest community members: