How to design an open position?

[Sylvain74]

Hello,

I have an Excel file with 2 tabs, the first tab contains list of purchase and sale contract with their respective quantities.

The second tab contains matching/marriage between one or many purchases and one or many sales.

I need to produce an open position of each contracts (whatever it is a purchase or a sale), as per below layout

CtrType	CtrRef	Counterparty	CtrQty	Jan 2020	Feb 2020	Mar 2020	....	Balance Qty
P	P-0001	Supplier 1	10'000	2'000		3'000		5'000
P	P-0002	Supplier 2	7'500					7'500
S	S-001	Customer 1	20'000	2'000		3'000		15'000

Since the number of columns varies over time and execution of contracts, I was thinking to use a Matrix but the problem is that the Row Total is summing the value from Jan to the end whereas I would like to calculate the remaining Balance Qty = CtryQty - SUM(MonthlyDeliveryQty)

How can I design such layout output? As I am not an expert, any suggestion is welcomed.

As data is not sensitive I could attached the PBIX File and Excel Source file but I don't know how to do it.... 😞

Thanks.

Sylvain

[v-robertq-msft · Accepted solution]

Hi, @Sylvain74 

According to your description, you can try this calculated column:

Balance Qty =

var _SUM_MonthlyDeliveryQty=[Jan-20]+[Feb-20]+[Mar-20]

return [CtrQty]-_SUM_MonthlyDeliveryQty

You can add more columns in the variable as you wish.

And you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sylvain74]

Hello Robert,

Thank you very for your suggestion! In the meantime I found a solution which is really closed to yours.

Here below are the steps in Power Query (Transform Data), I went through to achieve my goal.

1. Create a time table DimCalendar with a dedicated column for future headers (such as Prev. April 2020, April 2020..., Dec 2020, In 2021)
2. Make an inner join with my "Matching" table to get this new DeliveryPeriod column coming from DimCalendar
3. Remove all uncessary columns in order to avoid duplicated lines during the pivot step
4. Pivot the column DeliveryPeriod and sum over the delivered quantity.

Thanks

[v-robertq-msft]

Hi, @Sylvain74 

If you have solved your problem, would you like to mark my reply as a Solution, which can make more community members view and reference?
Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.