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

[BORAMKIM]

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

[lbendlin]

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-...

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/1447...

[BORAMKIM]

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.

[lbendlin · Accepted solution]

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

[BORAMKIM]

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

Thank you very much ! 🤝