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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BORAMKIM
Frequent Visitor

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

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.

BORAMKIM_1-1718179474093.png

Sincerely,

Kim,Boram

1 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:

 

lbendlin_1-1718412130478.png

 

 

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

 

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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

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-30984,249 
2023-04-1229,559,965 
2024-05-2110,187,488 

 

[Table2]

Cumulative Used
18,719,345

 

**Result 

[Table1]

Delivery Date[Amount (Ordered)Amount (Used)
2022-03-30984,249984,249
2023-04-1229,559,96517,735,093
2024-05-2110,187,488 

 

Thank you in advance.

 

 

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:

 

lbendlin_1-1718412130478.png

 

 

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

 

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

Thank you very much ! 🤝

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.