cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
perkses Regular Visitor
Regular Visitor

Subtract or Add #s to a date field in M Query

I would like to be able to subtract or add a # of days from a date field in the query editor.   In one column I have number of days (5,300, 60) and in another column i have dates 01/01/2018.  Is there a custom column function that will subtract or add?

 

 

Example......   01/01/2018 + [Date Field] (5) = 01/06/2018 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Subtract or Add #s to a date field in M Query

@perkses,

 

Change the data type if necessary.

Date.AddDays(Date.From([Start Date]), Number.From([Promote_or_Defer__c]))
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
MarcelBeug Super Contributor
Super Contributor

Re: Subtract or Add #s to a date field in M Query

Yes: Date.AddDays.

Specializing in Power Query Formula Language (M)
perkses Regular Visitor
Regular Visitor

Re: Subtract or Add #s to a date field in M Query

When I use the formula for dateadd it doesn't do the calculation.

 

Date.AddDays([Start Date], [Promote_or_Defer__c])

MarcelBeug Super Contributor
Super Contributor

Re: Subtract or Add #s to a date field in M Query

let
    Source = #table(type table[Start Date = date,Promote_or_Defer__c = Int64.Type], {{#date(2017,12,18),-10},{#date(2017,12,18),10}}),
    #"Added Custom" = Table.AddColumn(Source, "Resulting Data", each Date.AddDays([Start Date],[Promote_or_Defer__c]), type date)
in
    #"Added Custom"
Specializing in Power Query Formula Language (M)
Community Support Team
Community Support Team

Re: Subtract or Add #s to a date field in M Query

@perkses,

 

Change the data type if necessary.

Date.AddDays(Date.From([Start Date]), Number.From([Promote_or_Defer__c]))
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 51 members 1,218 guests
Please welcome our newest community members: