topic DAX_Subtract the Cumulative Used amount from the earliest Delivery Date's Amount (Ordered) in DAX Commands and Tips

DAX_Subtract the Cumulative Used amount from the earliest Delivery Date's Amount (Ordered)

BORAMKIM — Wed, 12 Jun 2024 09:40:40 GMT

Could you let me know the DAX which matches condition below?

### Requirements:

1. **Subtract the Cumulative Used amount starting from the earliest Delivery Date's Amount (Ordered)**.

2. **If the subtracted amount is greater than the Amount (Ordered)**

- Display the Amount (Ordered) in the Amount (Used) column.

- Subtract the remaining amount from the next earliest Delivery Date's Amount (Ordered).

- If the result is not greater than the Amount (Ordered), display the remaining amount in the Amount (Used) column.

Sincerely,

Kim,Boram

Re: DAX_Subtract the Cumulative Used amount from the earliest Delivery Date's Amount (Ordered)

lbendlin — Thu, 13 Jun 2024 00:52:38 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX_Subtract the Cumulative Used amount from the earliest Delivery Date's Amount (Ordered)

BORAMKIM — Thu, 13 Jun 2024 04:42:52 GMT

Thanks for the detailed explanation.

I am trying to substract the values based on one column to another columns.

Could you let me know the DAX which matches condition below?

DAX Query.xlsx

* Requirements:

1. **Subtract the Cumulative Used amount starting from the earliest Delivery Date's Amount (Ordered)**.

2. **If the subtracted amount is greater than the Amount (Ordered)**

- Display the Amount (Ordered) in the Amount (Used) column.

- Subtract the remaining amount from the next earliest Delivery Date's Amount (Ordered).

- If the result is not greater than the Amount (Ordered), display the remaining amount in the Amount (Used) column.

[Table1]

Delivery Date	[Amount (Ordered)	Amount (Used)
2022-03-30	984,249
2023-04-12	29,559,965
2024-05-21	10,187,488

[Table2]

Cumulative Used

18,719,345

**Result

[Table1]

Delivery Date	[Amount (Ordered)	Amount (Used)
2022-03-30	984,249	984,249
2023-04-12	29,559,965	17,735,093
2024-05-21	10,187,488

Thank you in advance.

Re: DAX_Subtract the Cumulative Used amount from the earliest Delivery Date's Amount (Ordered)

lbendlin — Sat, 15 Jun 2024 00:47:28 GMT

let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "NcnBDcAwCASwXXgfEhzQwixR9l8jUaX667WERqqFhglkOsEc2fgi1FKdNzioGsxT/6VaKf2eG7xfZLfsfQA=", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Delivery Date" = _t, #"Amount#(lf)(Ordered)" = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Delivery Date", type date}, {"Amount#(lf)(Ordered)", Currency.Type}} ), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn( #"Added Index", "Amount (Used)", each List.Accumulate( {0 .. [Index]}, [cm = Table2[Cumulative Used]{0}, rs = 0], (state, current) => [ rs = if #"Added Index"[#"Amount#(lf)(Ordered)"]{current} < state[cm] then #"Added Index"[#"Amount#(lf)(Ordered)"]{current} else state[cm], cm = if #"Added Index"[#"Amount#(lf)(Ordered)"]{current} < state[cm] then state[cm] - #"Added Index"[#"Amount#(lf)(Ordered)"]{current} else 0 ] ) ), #"Expanded Amount (Used)" = Table.ExpandRecordColumn( #"Added Custom", "Amount (Used)", {"rs"}, {"Amount (Used).rs"} ), #"Changed Type1" = Table.TransformColumnTypes( #"Expanded Amount (Used)", {{"Amount (Used).rs", Currency.Type}} ) in #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Or if you want it in DAX:

Used Column = var d = [Delivery Date] var cu = sumx(Table2,[Cumulative Used]) var c2 = sumx(filter(Table1,[Delivery Date]<d),[Amount(Ordered)]) var c = c2+[Amount(Ordered)] return if (c<cu,c,max(0,cu-c2))

Re: DAX_Subtract the Cumulative Used amount from the earliest Delivery Date's Amount (Ordered)

BORAMKIM — Tue, 18 Jun 2024 08:24:12 GMT

The code you provided was a great help to me and it works well.

Thank you very much ! 🤝