Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |