topic Re: Calculate the stock cost for items that haven't been shipped for 1 year in DAX Commands and Tips

Calculate the stock cost for items that haven't been shipped for 1 year

MikeRales — Mon, 25 Sep 2023 14:18:41 GMT

Hello,

I'm trying to calculate the stock cost value of items that have not been shipped for 1 year.

Here's my (simplified) data model. ILECosts are the cost of each Item Ledger Entry in my ERP.

The "Cout stock" measure calculates the daily stock cost. Its values are correct, I checked them separately with an SQL query. Here's the DAX measure code:

Cout Stock = CALCULATE ( SUM(ILECosts[Stock Cost]), FILTER(ALL(ILECosts), ILECosts[Posting Date] <= MAX(DIM_Date[Date])) )

I'm trying to create another measure that can filter the previous one, by only calculating the cost of Item Ledger Entries for items that have not been shipped for more than one year before the selected date range. A shipped Item Ledger Entry is defined by ILECosts[Document Type] = 1.

Here's an example of what I tried, but it's obviously wrong and too simplistic. I think I should summarize a subset of data by ILECosts[Item No_], to compute what has not been shipped for one year prior to the selected date range, but I don't understand how to do it.

Cout Stock sans expéd depuis 1 an = VAR Year_1 = DATEADD(DIM_Date[Date], -1, YEAR) VAR ILEShippingCostsOLD = CALCULATETABLE( ILECosts, FILTER ( ALL ( ILECosts ), ILECosts[Posting Date] <= MAX ( DIM_Date[Date] ) ), FILTER ( ALL ( ILECosts ), ILECosts[Document Type] = 1 ) ) VAR MaxShippingDateOLD = MAXX ( ILEShippingCostsOLD, ILECosts[Posting Date] ) VAR MaxShippingDate = ( CALCULATE ( MAX(ILECosts[Posting Date]), FILTER ( ALL ( ILECosts ), ILECosts[Posting Date] <= MAX ( DIM_Date[Date] ) ), FILTER ( ALL ( ILECosts ), ILECosts[Document Type] = 1 ) ) ) VAR x = --IF (MaxShippingDate <= Year_1, 1, 0 CALCULATE ( [Cout Stock], FILTER ( (ILECosts), ILECosts[Posting Date] >= MaxShippingDate ) ) --) RETURN x

I'll upload the .pbix file if necessary. Thanks in advance for any hint.

PS: my first post here. I hope everything is clear and clean enough!

Re: Calculate the stock cost for items that haven't been shipped for 1 year

lbendlin — Wed, 27 Sep 2023 00:30:32 GMT

This code

Cout Stock = CALCULATE ( SUM(ILECosts[Stock Cost]), FILTER(ALL(ILECosts), ILECosts[Posting Date] <= MAX(DIM_Date[Date])) )

is questionable and should be refactored.

Cout Stock = var md = MAX(DIM_Date[Date]) RETURN CALCULATE ( SUM(ILECosts[Stock Cost]), ILECosts[Posting Date] <= md )

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that 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.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculate the stock cost for items that haven't been shipped for 1 year

MikeRales — Wed, 27 Sep 2023 07:03:19 GMT

Thanks for the reply.

My formula for "Cout stock" is really what I need. I want the cumulative sum for all item ledger entries, from the very first ILE, up to the chosen date. I added to the .pbix the formula you suggested so you can see the difference.

Here's the link to the .pbix file

I want 2 things:

- a formula that calculates the cost for the Item Ledger Entries of "Item No_" that have not been shipped for more than 1 year before the selected date range.

An ILE for a shipped "Item No_" matches this criteria: ILECosts[Document Type] = 1.

The 1 year period must be variable (1, 2 or 3years).

- a chart that shows the "Cout stock" formula and the new one. I added this chart in the .pbix file.

Re: Calculate the stock cost for items that haven't been shipped for 1 year

lbendlin — Wed, 27 Sep 2023 13:46:32 GMT

I want the cumulative sum for all item ledger entries, from the very first ILE, up to the chosen date.

Define "very first". very first based on the user filters, or very first ignoring all filters?

Cout Stock = var mxd=MAX(DIM_Date[Date]) return CALCULATE ( SUM(ILECosts[Stock Cost]), ALLSELECTED(DIM_Date[Date]), ILECosts[Posting Date] <= mxd )

Re: Calculate the stock cost for items that haven't been shipped for 1 year

MikeRales — Wed, 27 Sep 2023 14:08:16 GMT

Very first chronologicaly, ignoring all filters.