I have to mix two tables and get a result by week

[Abevann]

Guys,

I have been working on a dashboard and got stuck; I have a couple of tables (SQL query) 

Date	Input	Output
20/01/2021		1
22/01/2021		1
02/02/2021		0
12/02/2021		1
08/03/2021		2
18/03/2021		1
23/03/2021		1
26/03/2021		1

Date	Monthly stock
01/01/2021	8
01/02/2021	6
01/03/2021	5
01/04/2021	0

On first table, I have the input and output from warehouse; in second one, I have the stock in warehouse. What I am looking for, is to get a table with the stock and substract the inputs or outputs of the warehouse as on attached table:

Product code	Year-week	Input	Output	Monthy stock	Weekly stock
UC48C2	2021-01			8	8
UC48C2	2021-04		2		6
UC48C2	2021-06		0	6	6
UC48C2	2021-07		1		5
UC48C2	2021-10			5	5
UC48C2	2021-11		2		3
UC48C2	2021-12		1		2
UC48C2	2021-13		2		0

Any suggestion for this issue?

Thanks a lot, regards,

Abelardo

[v-zhangti · Accepted solution]

Hi, @Abevann 

First, you need to append two tables in power query.

Then use the following calculation column to output the results you want.

Week = WEEKNUM([Date],1)

Weekly stock =
MAX ( Append1[Monthly stock] )
    - CALCULATE (
        SUM ( Append1[Output] ),
        FILTER ( Append1, [Week] <= EARLIER ( Append1[Week] ) )
    )

Year-week = YEAR([Date])&"-"&[Week]

The "week" column needs to be reserved, otherwise the view cannot be sorted.

Best Regards,

Community Support Team _Charlotte

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