topic Re: DAX: How to Multiply the Sum of a Column by the Maximum Value of Another Column in DAX Commands and Tips

DAX: How to Multiply the Sum of a Column by the Maximum Value of Another Column

MMPowerBI — Fri, 15 Mar 2024 15:01:07 GMT

Hi all,

thanks in advance for your support.

I'm facing a complex calculation with DAX that I'm not able to solve and I need support.

I have a fact table of inventory movements composed as follows:

ProductCode	Date	Quantity	UnitCost
x	01/01/2024	10	5
x	01/02/2024	-5	6
x	01/03/2024	20	7
y	01/01/2024	30	2
y	01/02/2024	10	3
y	01/03/2024	-20	4

The quantity in stock of a product at a given date is determined as the sum of the Quantity column from the point of time until the selected date.

On the other hand, the value of the inventory is to be determined as the product of the quantity in stock on the date (defined as above) for the last valid UnitCost with respect to the selected date (obviously per product, not the last valid cost ever).

For example: if I want to see the stock as of 02/03/2024 I would expect this result:

Product	Stock Quantity	Stock Value
x	25	175
y	20	80

Where:

StockValue of product x need to be multiplying the sum of the quantities in stock up to the desired date and the ultimate unit cost with respect to the selected date. In the case under analysis, the date of 02/03/2024 has been selected, there is no cost on this date because the last movement is the previous day so that is the cost to be considered to value the entire stock.

The same reasoning for the stock of product y.

I would need to write a measure that can calculate this stock value. The desire is to have a measure that works both if it is used in a PBI table that has the product as a row attribute but also as a total.

Can any of you give me some suggestions on how to do this?

Thank you so much in advance.

Re: DAX: How to Multiply the Sum of a Column by the Maximum Value of Another Column

lbendlin — Fri, 15 Mar 2024 17:36:08 GMT

That seems to be highly confusing. I played with your sample data a bit and here is what I came up with instead. Feel free to modify as needed

Re: DAX: How to Multiply the Sum of a Column by the Maximum Value of Another Column

MMPowerBI — Wed, 27 Mar 2024 19:19:54 GMT

Hi Ibendlin,

thank for reply.

The proposed solution is not the aspected one.

I probably didn't explain myself well.

let's consider the row dated 03/02/2024 in your example dataset.

in this case the quantity in stock is actually correct, it must be the sum of the quantities up to that moment (therefore 10-5=5). The value, however, must be calculated by multiplying the previously calculated quantity (therefore 5) with the last unit cost valid on that date (in this case the last valid cost for product x on the date of 02/03/2024 is the one that, in the original table I posted, refers to 02/01/2024 which is 6). So as a stock value on 02/03/2024 I would have expected to see 30 (5*6).
Furthermore, the calculation must be done per product because the unit value must be the latest for each product multiplied by the quantity in stock of the specific product).
On the total I would therefore expect to find the following value:
Stock value total = StockQtyX*LastUnitPriceX+StockQtyY*LastStockPriceY
Where:
- StockQtyX is the sum of the quantities of product
- LastUnitPriceX is the last unit price valid on the selected date (6 in the previous example)
Same considerations for Y.

I tried to sketch a solution like this:

- Measure: fx_StockUnitPrice = MIN('Movimenti'[Price])

- Measure:

LastStockUnitPrice = var _SelectedDate = MAX('Calendario'[Data])

RETURN CALCULATE(LASTNONBLANKVALUE('Movimenti'[Data], 'Movimenti'[fx_StockUnitPrice]), 'Movimenti'[Data]<= _SelectedDate)

- Measure:

StockValueBaseTable = var MainTable = SUMMARIZE('Movimenti', Movimenti[Prodotto], "LastStockUnitPrice", [LastStockUnitPrice], "Stock Qty", [Stock qty])

RETURN

SUMX(MainTable, [LastStockUnitPrice]*[Stock Qty])

Where table movimenti is the fact table i posted in the first post.

That solution works fine in a test case with few data.

When I implement that solution in the real case with over 50M data (There will be over 600M when fully operational) this solution goes in "exceded resource limits" of the powerBI report.

Is there a way to achieve the same result more efficiently?

Thanks in advance

Re: DAX: How to Multiply the Sum of a Column by the Maximum Value of Another Column

lbendlin — Wed, 27 Mar 2024 19:38:30 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: DAX: How to Multiply the Sum of a Column by the Maximum Value of Another Column

MMPowerBI — Wed, 27 Mar 2024 19:57:20 GMT

Hi,

I have a fact table like that:

The aspected result are:

- If i select the date of 01/01/2024 on the report slicer:

The following result, boot by product and total:

- If i select the date of 02/02/2024 on the report slicer:

- If i select the date of 03/03/2024 on the report slicer:

- If i select the date of 04/04/2024 on the report slicer:

the reasoning of this last case, but the same applies to the previous ones is:
- The StockQty column must be the sum of the movements from the dawn of time until the selected date (04/04/2024). Which, for the product x is 100 (i.e. the sum of its movements up to the date: 100+20+30-50), for the product Y it is 50 (100-80+20+10).
- The LastStockUnitPrice column is, for each product x, the last unit cost valid on the date. For the product, the last valid cost is the one corresponding to the record of the original dataset as of 04/04/2024, i.e. 16, for the product Y 22. Obviously this measurement on the total line makes no sense.
- The StockValueBaseTable column is the product, per row, of the two previous values. This is the real value I want to get. On the total line, the result to be obtained is not the product of the two previous measurements on the total line, but rather the sum of the products by product.