Subtract days power query BI

[bhelou]

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,

[Greg_Deckler · Accepted solution]

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"

[Mariusz · Accepted solution]

---

---

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

[Mariusz · Accepted solution]

---

---

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]

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 ,

Many Thanks ,

[Jimmy801 · Accepted solution]

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]

@Jimmy801 

Thank you very much it worked .

Regards,

[Jimmy801]

Hello @bhelou 

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

Jimmy

[Greg_Deckler · Accepted solution]

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"