Power Query - Cumulated row

[marry]

Hi everyone,

Would like to have the new added rows display in my Power BI Matrix table to calculate the EOH Quantity, the logic is a little bit complicated, please check below detailed requirement. What I need in the end is to implement it in Power BI report.

Hope the experts can help on this, it will save my day. Thank you.

Requirement:

Yellow rows are the expected to-be-generated views in my PBI report

• EOH generation need to be calculated Per Product Name + OSAT Supplier (for example, one Product Name with 2 different OSAT suppliers, need to generate 2 EOH rows)
• EOH for current month= (Current month TK Supply BOH Qty sum + TK Supply Qty sum+ CS Supply BOH Qty sum + CS Supply Qty sum) –Demand
• EOH for the following month = (Previous month EOH + TK Supply Qty sum + CS Supply Qty sum)-Demand
• Report sequence sorted by “Identifier” column: Demand>> TK Supply>> CS Supply>> EOH

Here is my PBI file: (It seems I cannot attach the pbi file directly, please let me know if you can open it.)

https://drive.google.com/file/d/1PWoOq2Y2iP45ykuD5CnP5lD_dAIMFrYi/view?usp=sharing

Excel sample result calculation:

https://docs.google.com/spreadsheets/d/1MVP8ptlkKTT-AssKP2rlI0MKbPJirhFS/edit?usp=sharing&ouid=10968...

Cheers

Marry

[ronrsnfld]

Your pbi file is not useful as the Source refers to a file in your personal workspace, which is not accessible.

[marry]

Hi @ronrsnfld ,

I put the excel source file here, 

https://docs.google.com/spreadsheets/d/17ut6MHGqxfiGui5RbmIYJBS4jUvjXlxh/edit?usp=sharing&ouid=10968...

My current PBI Matirx  table look like below:

My PBI Source data: (Please get the data from above excel which I provided in the google drive)

Cheers,

Marry