Production calendar with accumulated balance

[Anonymous]

Production calendar with remaining balances

Hello,
I’m trying to make a production calendar with data from ERP system. I have the following data:

I want to get the data like this in Matrix table or similar:

The idea is to get the Remaining amount as Ordered amount for the next date. Unfortunately from the ERP system I can get only the Total Ordered Qty for each row, but not accumulated. So I want to make the report in PowerBI.

I’m attaching the Excel file as well.

Thanks!

[v-huizhn-msft]

@Anonymous,

Where is your Excel file attachment?

Thanks,
Angelia

[Anonymous]

I could't upload the file, then I have read that normal users cannot upload files.

So I uploaded it here:

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

[Anonymous]

Any help here?

Thanks!

[v-huizhn-msft]

Hi @Anonymous,

Create a measure using the formula:

Remaining Qty = SUM(Table3[Ordered Qty])-SUM(Table3[Produced Qty])

Create a matrix visual, select [Product] as rows, the [Doc Date] as column level, [Ordered Qty] [Produced Qty] and [Remaining Qty] as value level(please see the highlighted in red line), you will get expected result.



Best Regards,
Angelia

[Anonymous]

I think you did't understand me. ERP system gives information only for the whole ordered quality and for every contract and product i got the same qunatities, as you can see on the picture (6000 and 4000pcs). I need information for each day for each product. The information i need to see is in the seoond printscreen. I will explain again:

Day 1 i have Ordered Qty - 6000, produced 3400 and remaining 2600qty.

Day 2 i have Ordered Qty 2600qty (6000-3400), produced 760, reamining 1840.

And so on, so on.

I hope this time I have explained more understandable. If not, please check the excel file.

Thanks!

[v-huizhn-msft]

Hi @Anonymous,

Yes, based on my understanding, you can load the table below from ERP system.



Then, you want to get the following matrix.



Best Regards,
Angelia

[Anonymous]

Yes, but i I can not get the updated values in column Ordered Qunatity, it always show the whole quantity.