Hi all.
I'm gonna be crazy with this. I'm learning DAX so it'd be easiest than I can reach.
I've to tables. One with daily production and another one with daily sales. In first one there are two fields, item code and source code, among others. This is: for each source code there are many item codes. It means there isn't a unique simple key.
For ex:
Source Code ------- Item Code ----- Output Qty --- Date
A0001 --------------- F000001 --------- 100 --------- 10/01/2019
A0001 --------------- F000020 --------- 200 --------- 10/01/2019
A0002 --------------- F000001 --------- 50 ---------- 12/02/2019
A0002 --------------- F000023 --------- 250 ---------- 13/03/2019
The key should be Source_Code + Item_Code
The other table is a standar sales table. This is
Item Code --------- Qty ------ Date ------------- Customer No.
F000020 ----------- 25 ------- 25/06/2019 ------ 0001
F000020 ----------- 150 ------ 23/02/2019 ------ 0002
F000023 ----------- 40 ------- 12/05/2019 ------- 0001
....
I can't relate tables cause it'd be many-to-many, just with a dates table.
I've a measure with outTotal := SUM(production[Output Qty])
It works fine and I can get total output produccion by Source Code and Item Code.
But I cannot get a measure or calculated column to calculate sales from an item only.
I tried
CALCULATE(SUMX(Sales; Sales[Qty]); ALLEXCEPT(Sales;Sales[Item_Code]))
CALCULATE(SUMX(Sales; Sales[Qty]); FILTER(Sales; Sales[Item_Code] = Production[Item_Code]))
But it doesn't work cause is sums one time per Item_Code in Source_Code in dynamic table.... 😞
Could you help me, please?
Regards
