Track Inventory value

[Namarcil]

Hello everyone, 

i'm in fight for many hours now to get a solution to my problem 

I have this Dax formula to compute a cumulated addition to get a daily stock snapshot.

the formula here 

Images de stock = CALCULATE(SUMX('LIGNES',LIGNES[Quantités pour stock]),'Calendrier'[Date]<=max('Calendrier'[Date]))

Where 

LIGNES[Quantités pour stock]

is a column I have created in my Table "LIGNES" which is the fact table regrouping all Types of movement and all transactions.

It works pretty well. and provide the expected results

However we can notice that my prices changed quite often during the holding item period , it works well on both tables

The problem is that the last dated Purchasing price ([Historique PA statique])  need to be multiplied by the Stock Snapshot   and by the last purchasing price.

Now I would like to get the Stock Value,

Images de stock = CALCULATE(SUMX('LIGNES',LIGNES[Quantités pour stock]),'Calendrier'[Date]<=max('Calendrier'[Date]))

Historique PA Statique =
IF(
    HASONEVALUE(LIGNES[ARTICLE_KEY]),
    CALCULATE(
        SUMX(
            LIGNES,
            LIGNES[Dernier prix achat ligne]
        ),
        LIGNES[Libelle nature de mouvement] = "Facture fournisseur", 
        ALL(LIGNES[ARTICLE_KEY])
    ),
    BLANK()

)

 Which finaly make 

Stock value = [Images de stock]*[Historique PA Statique] 

= something like this 

How can i achieve this since [Dernier prix d'achat] (the column in the table "LIGNES")  must be taken only on LIGNES'[Libelle nature fournisseur] = "Facture fournisseur" and only based on one quantity 

like this table 

 🙂 

Thank you in advance for any help !

[danextian]

Hi @Namarcil ,

Please post a workable sample data, not an image/s.

[Namarcil]

Hello,

my model is really heavy

and i don't know exactly how i can show you , maybe with a picture of my model?

if it can help, Here i only need two tables , LIGNES (Fact table) and Calendrier (Calendar) 

Linked together on LIGNES'[Date ligne] and Calendrier[Date]

in tbale "LIGNES" i have created this column 

Quantités pour stock =

        VAR QuantiteAjustee = SWITCH (
            'LIGNES'[Libelle nature de mouvement],
            "Sorties exceptionnelles", - ABS('LIGNES'[Quantités mouv stock]),
            "Transfert émis", - ABS('LIGNES'[Quantités mouv stock]),
            "Ticket", IF('LIGNES'[Quantités mouv stock] > 0, - ABS('LIGNES'[Quantités mouv stock]), ABS('LIGNES'[Quantités mouv stock])),
            "Réservations clients", IF('LIGNES'[Quantités mouv stock] > 0, - ABS('LIGNES'[Quantités mouv stock]), ABS('LIGNES'[Quantités mouv stock])),
            'LIGNES'[Quantités mouv stock]
        )
        RETURN IF(QuantiteAjustee < 0 && 'LIGNES'[Prix unitaire HT net ligne] < 0, ABS(QuantiteAjustee), QuantiteAjustee)

Which represents The Quantities on lines which affect the stock levels.

i have created this cumalated stock levels measure 

Images de stock = CALCULATE(SUMX('LIGNES',LIGNES[Quantités pour stock]),'Calendrier'[Date]<=max('Calendrier'[Date]))

Which provide me with the expected results (as per shows previously)

Which take the total quantities of each days, and repeat the value if no transaction.

I Can have also the last purchase price per items aggregated by days when we have price changes

So for first example my value stock should be 536 * 658.4 = 352902.4

With my formula 

Cumulative Historique PA = [Images de stock]*[Historique PA Statique 2]

i obtain 352902.4 which is correct, 

however i cannot repeat this informatio accross lines 

How can I manage this?

🙂 Sorry if i cannot send my file :s