Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
bhelou
Responsive Resident
Responsive Resident

Subtract days power query BI

HI ,

 

Please i need to subtract 10 days from this function as attached , please anyone can help me with the formula .

 

=if[Day 2]="0" then [Last INV Date] - 10 else [Last INV Date]

 

"0" is the value of what i want to subtract 10 days of

Thanks,

 

 

3 ACCEPTED SOLUTIONS
Greg_Deckler
Community Champion
Community Champion

I believe you want Date.AddDays([Last INV Date], -10)

 

Here is an example query I created:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyUIrViVYyAAoYIQsYAQWM4QKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Last INV Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Last INV Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Item] = "0" then Date.AddDays([Last INV Date],-10) else [Last INV Date])
in
#"Added Custom"



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Mariusz
Community Champion
Community Champion



Hi @bhelou 

 

Try this.

 

if [Day 2]="0" then Date.AddDays(  [Last INV Date], -10 ) else [Last INV Date]

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

View solution in original post

Jimmy801
Community Champion
Community Champion

Hello @bhelou 

 

the value is a number, non a text. so use this formula

if [Day 2]=0 then Date.AddDays( [Last INV Date], -10 ) else [Last INV Date]

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

6 REPLIES 6
Mariusz
Community Champion
Community Champion



Hi @bhelou 

 

Try this.

 

if [Day 2]="0" then Date.AddDays(  [Last INV Date], -10 ) else [Last INV Date]

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn


 

bhelou
Responsive Resident
Responsive Resident

Dear @Mariusz ,

 

The formula not is working it is givving me data but not subtracting at all , it is remaining the same ,

 

if [Day 2]="0" then Date.AddDays( [Last INV Date], -10 ) else [Last INV Date]

 

Attached image ,

Image.JPG

Many Thanks ,

 

 

Jimmy801
Community Champion
Community Champion

Hello @bhelou 

 

the value is a number, non a text. so use this formula

if [Day 2]=0 then Date.AddDays( [Last INV Date], -10 ) else [Last INV Date]

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

bhelou
Responsive Resident
Responsive Resident

@Jimmy801 

 

Thank you very much it worked .

 

Regards,

 

 

Jimmy801
Community Champion
Community Champion

Hello @bhelou 

 

I've also marked the solution of @Greg_Deckler  as solution (can only mark one as solution)

 

Jimmy

Greg_Deckler
Community Champion
Community Champion

I believe you want Date.AddDays([Last INV Date], -10)

 

Here is an example query I created:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyUIrViVYyAAoYIQsYAQWM4QKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Last INV Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Last INV Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Item] = "0" then Date.AddDays([Last INV Date],-10) else [Last INV Date])
in
#"Added Custom"



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors