Pandas (Python) in powerBI LOOP

[Anonymous]

Good evening please i need your help I wrote this code to calculate the final stock; the problem is that I want to apply a loop on 'code article' and 'division'. each article in one of its two divisions has its own stock

the stock of the article in the 2 divisions is independent

'dataset' holds the input data for this script
import pandas as pd

DX= pd.DataFrame(dataset.loc[:,])

AAA = []

for i in range(len(DX)):

if(i==0):
var = DX['Stock 10 fixe'][0] + DX['Livraison'][i]-DX['consomation'][i]
AAA.append(var)
else:

var = var + DX['Livraison'][i]-DX['consomation'][i]

AAA.append(var)DX['stock Final']=AAA

the result of my code  is false (only good for M100 and DPC1 i want that ( every 'code article' in every 'Division' has his own stack of the last 4 months  

[Anonymous · Accepted solution]

Hi @Anonymous ,

I'm not sure if it's possible to achieve the result you want with phython, but it can be achieved with DAX... I create a sample pbix file(see attachment), please check whether it can work as a workaround.

1. Add Index column group by Code Aritcle and Division according to month (as shown below) in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4MgEIb/C7O5wHEKznUi6cduHKph6NCaGOPvL2iaGBAaWQ4uD8fD27bsKjhnBWseF+EKluvpPsxjbye3EwJIucpZV4Sw397Gxb77yfq7Gmq9zUjAjR1+sCSo1gd5CjbPz/LaHCoNvkvhYOO7UobKBLqMlc2BskQgryxDC3OgrDjo2isTJeCdstKAXkKU9Qbj/n/qT8x4JuYIzsUcwZmYcf+/yq1czHgm5gjOxRzBqZi7Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code Article" = _t, Division = _t, #"Stock 10 fixe" = _t, Month = _t, consomation = _t, Livraison = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code Article", type text}, {"Division", type text}, {"Stock 10 fixe", Int64.Type}, {"Month", type text}, {"consomation", type number}, {"Livraison", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code Article", "Division"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table [Code Article=nullable text, Division=nullable text, Stock 10 fixe=nullable number, Month=nullable text, consomation=nullable number, Livraison=nullable number, Index=nullable number]}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}, {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"})
in
    #"Expanded Index"

2. Create a measure or calculated column as below to get the final stock

Measure:

stock Final = 
VAR _selcodea =
    SELECTEDVALUE ( 'Table'[Code Article] )
VAR _seldiv =
    SELECTEDVALUE ( 'Table'[Division] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] )
            - SUM ( 'Table'[consomation] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Code Article] = _selcodea
                && 'Table'[Division] = _seldiv
                && 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
        )
    )

Or Calculated column

Column_stockFinal = 
    CALCULATE (
        SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] )
            - SUM ( 'Table'[consomation] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Code Article] = EARLIER('Table'[Code Article])
                && 'Table'[Division] = EARLIER('Table'[Division])
                && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
        )
    )

Best Regards