Running Total accros dates, item ID, Warehouse , Specific movement Type

[SebSchoon1]

Hi guys,

Great challenge for you!

i have a huge table with item movements (positives and negatives). between multiple warehouses (10 years of data for around 12 millions rows)

I would like to calculate at a specific date The total in stock for each warehouse.

Here' what i can Get

This table has been created using summarize on database lines.

Heres the formula

Images de stock = SUMMARIZE(FILTER('BDD - Lignes de mouvements','BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Avoir fournisseur sur stock" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Ecarts d'inventaire" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Entrées exceptionnelles" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Facture fournisseur" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Réception fournisseur" ||'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Retour fournisseur" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Sorties exceptionnelles" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Ticket" || 'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Transfert émis" ||'BDD - Lignes de mouvements'[LIBELLE NATURE DE MOUVEMENT]="Transfert reçu"),'BDD - Lignes de mouvements'[DATE DE MODIFICATION],'BDD - Lignes de mouvements'[CLEF UNIQUE ARTICLE],'BDD - Lignes de mouvements'[DEPOT],"Quantités en stock",CALCULATE(SUM('BDD - Lignes de mouvements'[QUANTITE])))

In Short i calculate only the movement types (which are in 'lignes de mouvements'  Table in a column called [NATURE DE MOUVEMENT]

Like this I have the positive moves (entries) and negative ones ( Sellings, transfers, credit notes,...)

This allows me to get an image of stock by Item.

The problem is that i also have huge movement problems between warehouses.

Like transfers never received of like 700 items.

So -700 Warehouse A

Never received Warehouse B

In then years this creates huge discrepancies!

i would like to avoid this! So i need a running Total By Items, and when to total per day is 0 or less in the warehouse or in total quantities, it must not show anything.

The result i woul like is.

[amitchandak]

@SebSchoon1 , You have try a measure like

Cumm = CALCULATE(SUMX(values(date[date] ) , [Image De Stock]),filter(allselected(date),date[date] <=max(date[Date])))

or

Cumm = CALCULATE(SUMX(values(date[date] ) , [Image De Stock]),filter(all(date),date[date] <=max(date[Date])))

[SebSchoon1]

Hello amitchandak I try this tomorrow! Many thanks in advance for your answer!

[SebSchoon1]

Mister, one additional question if it work tomorrow,

How can I say. 

If result is 0 or minus 0 do not show the stock level ?

This to correct wrong calculations

[SebSchoon1]

Hello @amitchandak it has not worked as desired output.

When I select a date in the calendar. The calculated value is not the running total of the items in stock at the selected date.

It only calculate the item movements of that specific date. 

Not of the previous moves concerned by this same item.

Any advice?

[SebSchoon1]

Hello, any supplementary advice?