Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago
Solved

Stock Valuation

I have two tables, one with the average prices and one with the stock movements.

The price table is as follows

ID Date __articulo __id __nuevopmc

2827/01/202126262,5297
5729/01/202126262,6243
38410/03/202126262,6791
60005/04/202126262,6659

On the one hand I need to fill in the price for those months that there is no average price, for example in February I have no price. And this price has to be that of the last month with price, and in case there are several you have to take the one with the highest ID.

I already asked this in the forum and they answered me with the following formula, which for my needs was perfectly valid:

Max Price: =
WHERE _item =
.MAX ( fPMC[__articulo__id] )
WHERE _newtable =
CALCULATETABLE (
fPMC,
TREATAS (
GROUPBY (
SUMMARIZE (
FILTER ( fPMC, fPMC[__articulo__id] = _item ),
dFechas[Date],
fPMC[id],
fPMC[__nuevopmc]
),
dFechas[Date],
"@maxid", MAXX ( CURRENTGROUP (), fPMC[id] )
),
dFechas[Date],
fPMC[id]
)
)
RETURN
SUMX ( _newtable, fPMC[__nuevopmc] )
Last Price =
CALCULATE (
LASTNONBLANKVALUE ( dDates[Date], [Max Price:] ),
dFechas[Date] <= .MAX ( dFechas[Date] )
) + 0

Everything works correctly until I have to value these stocks. Total stocks is not the multiplication of the sum of accumulated stock by the sum of last prices.

And taking into account that the articles are grouped into products, classes and subclasses .....

I leave the example .pbix in dropbox that I have created in power BI so you do not waste time creating a model and loading data.

https://www.dropbox.com/s/yyrkf6qej9nic59/Ejemplo%20Valoraci%C3%B3n.pbix?dl=0

Thank you very much in advance

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Syndicate_Admin ,

     

    Please try:

     

    Measure = 
var _a = SUMMARIZE('tArticulos',tArticulos[Articulos],"StockAC",[Stock Ac],"UltimpoPrecio",[Ultimpo Precio])
return SUMX(_a,[StockAC]*[UltimpoPrecio])

     

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Syndicate_Admin ,

     

    Please try:

     

    Measure = 
var _a = SUMMARIZE('tArticulos',tArticulos[Articulos],"StockAC",[Stock Ac],"UltimpoPrecio",[Ultimpo Precio])
return SUMX(_a,[StockAC]*[UltimpoPrecio])

     

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      Let me try it on the real model and I confirm that everything is fine. At the moment the solution looks very good.

      Thank you very much for raising it.