Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all! First off, thanks in advance for any help anyone can provide, it is greatly appreciated!
I have done a lot of searching, and can't quite find anything that I can get to work. As someone who is very much a Power BI novice, I'm certain that the problem lies with me, but if anyone can walk me through a solution it would be absolutely wonderful.
Here's the situation:
I have 2 tables that I am pulling from D365: 'InventItemPrices' and 'InventorySitesOn-Hand'. The 'InventorySitesOn-Hand' table provides [ItemNumber] as well as the [OnHandQuantity], but no Value for the on-hand quantity. While 'InventItemPrices' provides [Price] for each [ItemNumber]. However each [ItemNumber] is shown multiple times, based on the [PriceCreatedDateTime] that the [Price] was updated in the system.
So for example, in the 'InventItemPrices' table, it would show [ItemNumber] X00123456 5 times, each time with a different [PriceCreatedDateTime].
Whether it's in the 'InventItemPrices' table or the 'InventorySitesOn-Hand' table, I'm trying to be able to have just the [Price] for the latest [PriceCreatedDateTime] so that I can then multiply it by the [OnHandQuantity] and get the actual on-hand value.
I'm trying to do this creating a new column instead of a measure, only because the resources I was able to find used that as the solution. However after doing a lot of searching, I was able to only get as far as getting a column that yields the last [PriceCreatedDateTime] but not the actual Value per [ItemNumber] corresponding to that last date. 😞
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario.
InventorySitesOn-Hand:
InventItemPrices:
There is many-to-one relationship between 'InventItemPrices' and 'InventorySitesOn-Hand' tables.
You may create a calculated column in InventorySitesOn-Hand as below.
Result =
var _itemnum = 'InventorySitesOn-Hand'[ItemNumber]
var _latestdatetime =
CALCULATE(
MAX(InventItemPrices[PriceCreatedDateTime]),
FILTER(
ALL(InventItemPrices),
InventItemPrices[ItemNumber] = _itemnum
)
)
var _price =
CALCULATE(
SUM(InventItemPrices[Price]),
FILTER(
ALL(InventItemPrices),
InventItemPrices[PriceCreatedDateTime] = _latestdatetime&&
InventItemPrices[ItemNumber] = _itemnum
)
)
return
[OnHandQuantity]*_price
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft THANKSS!! i've been trying for days!
It helped me a lot! thanks for your help!
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario.
InventorySitesOn-Hand:
InventItemPrices:
There is many-to-one relationship between 'InventItemPrices' and 'InventorySitesOn-Hand' tables.
You may create a calculated column in InventorySitesOn-Hand as below.
Result =
var _itemnum = 'InventorySitesOn-Hand'[ItemNumber]
var _latestdatetime =
CALCULATE(
MAX(InventItemPrices[PriceCreatedDateTime]),
FILTER(
ALL(InventItemPrices),
InventItemPrices[ItemNumber] = _itemnum
)
)
var _price =
CALCULATE(
SUM(InventItemPrices[Price]),
FILTER(
ALL(InventItemPrices),
InventItemPrices[PriceCreatedDateTime] = _latestdatetime&&
InventItemPrices[ItemNumber] = _itemnum
)
)
return
[OnHandQuantity]*_price
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear @v-alq-msft
I am trying to apply the formula you posted here to a very similar dataset to the one you created in your post. The only difference is that I have more than one entry in InventorySitesOn-Hand table for each itemNumber. I mean I have the closing balance inventory for each item for each month end, so my table looks like the one below:
I have change the field of your formula with the corrsponding field in my dataset but an issue with the var -lastestdatetime appears and I cant not figure out how to solve it.
Below the formula of my dataset:
inv_value =
var _itemnum = Inventarios[art]
var _latestdatetime =
CALCULATE(
MAX(ARTIMP[costo_unitario_usd];
FILTER(
ALL(ARTIMP);
ARTIMP[art] = _itemnum
)
)
var _price =
CALCULATE(
SUM(ARTIMP[costo_unitario_usd]);
FILTER(
ALL(ARTIMP);
ARTIMP[fecha] = _latestdatetime&& ARTIMP[art] = _itemnum
)
)
return
[cant]*_price<p> I will appreciate very much your help to solve this issue.</p><p>Best regrads,</p><p>Andrés</p>
@v-alq-msft Allan - thank you SOOOOO much for your help! I had been spinning my wheels for days trying to research this and figure this out to no-avail. I am very much appreciative!
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
101 | |
97 | |
66 | |
59 |