Get Qty SUM and Daily Opening /Closing totals from another table by date and item key

[SARVAN27]

Hello dear friends!

I need your help! I need to get SUM of Qty from another table

I have 3 tables
Items, Sales and Receipts

Relationship through the ItemId field across three tables. There is also a Calendar table with a Date field linking to the Sales.Date table.

ITEMS Table

Itemid	Itemname	ItemGroup	Price
68992	ItemA	A	50
101187	ItemB	A	80
105083	ItemC	B	70
105084	ITemD	B	40

RECEIPTS Table (WHInput)

Date	Itemid	Wh	Qty	Column1
08.01.2022	68992	W351	30	1
03.01.2022	101187	W351	34	1
07.01.2022	105083	W351	26	1
01.01.2022	105084	W351	44	1
07.01.2022	68992	W350	54	1
04.01.2022	101187	W350	33	1
03.01.2022	105083	W350	23	1
06.01.2022	105084	W350	45	1
06.02.2022	68992	W351	26	2
02.02.2022	101187	W351	46	2
05.02.2022	105083	W351	24	2
02.02.2022	105084	W351	31	2
08.02.2022	68992	W350	53	2
08.02.2022	101187	W350	42	2
06.02.2022	105083	W350	43	2
01.02.2022	105084	W350	41	2

SALES Table

Date	Itemid	CustId	Qty	Price	Discount	Amount	WH	Month
09.01.2022	105084	CUS1	1	40	0	40	W351	1
09.01.2022	105084	CUS1	2	40	10	72	W350	1
09.01.2022	105084	CUS2	2	40	20	64	W351	1
09.01.2022	105084	CUS6	1	40	10	36	W350	1
09.01.2022	105084	CUS1	2	40	20	64	W351	1
09.01.2022	105083	CUS1	1	70	15	59,5	W350	1
09.01.2022	68992	CUS4	2	50	15	85	W351	1
09.01.2022	68992	CUS1	1	50	15	42,5	W350	1
09.01.2022	101187	CUS2	1	80	0	80	W350

Now I need to get for each day in the calendar the Opening Qty = Starting SUM(Qty) for that day, Total Input Qty = SUM(Qty) up to this day from the Receipts table, Amount of Sales on that day , Input Qty Current day = SUM(Qty) on this day from the Receipts table, and Closing Qty, taking into account the previous variables.

I tried a lot of options - unfortunately I can not get the correct result.

Total Input Qty = formula is correct if only 1 product is selected, and as soon as I select 2 or more products, it sums up everything for the selected products, but I need only for the product in the sales line.

Total Input Qty =

varsalesQty =CALCULATE(

 SUM( Sales[Qty] ),

 FILTER(ALL( 'Calendar' ), 'Calendar'[Date] <=MIN( 'Calendar'[Date] ) )

  )

varinputQty =CALCULATE(

 SUM( WhInput[Qty] ) ,//KEEPFILTERS(

  WhInput[Date] <=MIN( 'Calendar'[Date] )

  )

returnif(ISBLANK(salesQty), blank(), inputQty)

Opening QTY =
var salesQty = CALCULATE (
SUM ( Sales[Qty] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) )
)
var inputQty = CALCULATE(
SUM ( WhInput[Qty] ) , //KEEPFILTERS(
//WhInput[Date] < MIN ( 'Calendar'[Date] )) //ok
FILTER ( 'WhInput', WhInput[Date] <= MIN ( 'Calendar'[Date] ))
)
var inputQtyX = IF(ISBLANK(salesQty ), BLANK(), IF(ISBLANK(inputQty), BLANK(), inputQty - salesQty))
RETURN inputQtyX

And I'm not really sure about the Total Input Qty MAX = formula, because for a date without a sale, the Input qty shows empty.

Total Input MAX =
CALCULATE(
SUM ( WhInput[Qty] ) ,
FILTER ( 'WhInput', WhInput[Date] <= MIN ( 'Calendar'[Date] )
&& WhInput[Itemid] = MIN(Sales[Itemid])
)
)

Can you help - how to build functions in order to get the correct result from this data?

I really appreciate any help! Thanks in advance!

[lbendlin]

Now I need to get for each day in the calendar 

you didn't include the calendar table in your sample data.  Do you want it to be linked into the data model or do you want to keep it separate for scenarios where you have days without sales?

What is the significance of the "Wh" column? Is this data model appropriate for your business scenario?

[SARVAN27]

Do you want it to be linked to the data model or do you want to keep it separate for scenarios where you have no sales days?
I need to be able to find the input, open and close quantity for any day in the calendar, even if there are no sales for that item on that day.

Thanks again!

[lbendlin]

Here would be an example of a daily breakdown by item and warehouse for sales.  Can you please indicate what the business logic for the opening and closing balance would be?  For days without sales they would stay the same?

[SARVAN27]

Hi @lbendlin ! Thank you for your valuable time!

The business logic is such that if a product has not been sold for a certain period, for example, the first or second week of the month, or the first 10 days from the moment it first arrived at the warehouse, even if there were no sales, then it must be displayed as an opening balance and final balance. This is necessary for turnover statistics. The question will arise - why display days if there was no movement, but if you look in the context of several goods or groups of goods, then you need to take into account the start and final balance, taking into account all filtered goods - it does not matter if there was a sale or arrival of any goods or not.

Figuratively speaking, if item 68992 arrived at warehouse 651 on January 10 and the first sale is only 10 days later, then we need to display all these dates from 01 to 11 januar with the start and final balance.

Thank you so much!

[SARVAN27]

Hi @lbendlin!

Thank you for your reply!

Here my attached PBI file.

WH = warehouse, WhInput = RECEIPTS TABLE.

PBI_Test 

I need to find a qty total for each day for each item and then for each warehouse for calendar days.

nd based on this value I need to calcualte Opening and closing qty balance .

Thanks in advance!