Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
Solved! Go to 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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
4 | |
4 | |
3 | |
3 | |
3 |