Combine rows from multiple columns into a new column

[matteomatilde]

Hello everyone.

I've got these two tables:

• FACT_STK

STK_ID	STK_ITM_ID	STK_DATE	STK_QTY	STK_WHS
1	I0154	24 Apr 21	50	WHS1
2	I0154	26 Apr 21	30	WHS1
4	I0154	30 Apr 21	20	WHS1
5	I0154	2 May 21	15	WHS1

• FACT_MOV

MOV_ID	MOV_ITM_ID	MOV_DATE	MOV_QTY	MOV_CAU	MOV_WHS
1	I0154	25 Apr 21	20	Sale	WHS1
2	I0154	1 May 21	5	Sale	WHS1

As you can see from the stock quantities, 10 pieces of I0154 were sold in a day between 26 and 30 April, but no movements were registrated in the FACT_MOV.

In addition, 5 pieces of I0154 wew sold on May 1st, but the stock was updated the day after.

My aim is to calculate the average stock for each item, calculated as the sum of (stk_qty * days_of_stock) divided by (total days of stock)

so I thought that I could create a table with all the dates (from the minimum date of fact_mov and fact_stk to the maximum date) and create some columns like these:

ITM_ID	DATE	STK_QTY	MOVEMENT	CORRECT_STK	WHS_ID
I0154	24 Apr 21	50	0	50	WHS1
I0154	25 Apr 21	50	20	30	WHS1
I0154	26 Apr 21	30	0	30	WHS1
I0154	27 Apr 21	30	0	30	WHS1
I0154	28 Apr 21	30	0	30	WHS1
I0154	29 Apr 21	30	0	30	WHS1
I0154	30 Apr 21	20	10	20	WHS1
I0154	1 May 21	20	5	15	WHS1
I0154	2 May 21	15	0	15	WHS1

How can I implement it in Power BI? Do you have a smarter idea to achieve my goal? I leave you also the pbix file: https://www.dropbox.com/s/m2z3g5xf19n7ji8/matteo%20-%20Copia.pbix?dl=0

Thanks.

[CNENFRNL]

Tweak the code according to your own dataset,

[matteomatilde]

Thank you so much, it worked! Do you know how can I calculate the average stock, intended as sum of (CORRECT_STK* numbero of days of stock) divided by (total days of stock)

In the example it would be (50*1 + 30*4 + 20*2 + 15*2) / 9 = 26,67 days

Thank you!