Qty monthly balance based on initial inventory (data provided)

[henrique0galli]

Hello everyone,

I want to achieve the following outcome:

Basically I want the Item column and the inbound quantities of this part each month. Based on the current Available Stock, I want to calculate the balance of each month reflecting the balance of the previous month.

(data provided below)

If I choose a matrix or table, the Available Stock repeats on each month, which is wrong -- as this column should appear just once to calculate the rest:

DATA PROVIDED

Inbounds:

Delivery Date	Item	Qty
07/05/2018	C2798-00	270
07/05/2018	C2798-00	300
16/05/2018	C2798-00	50
01/07/2018	C2798-00	700
16/05/2018	C2862-03	3500
16/05/2018	C2862-03	1950
07/05/2018	C2930-01	100
07/05/2018	C2930-01	100
07/05/2018	C2930-01	42
07/05/2018	C2930-01	100
16/05/2018	C2930-01	62
16/05/2018	C2930-01	62
16/05/2018	C2930-01	62
16/05/2018	C2930-01	54
01/07/2018	C2930-01	115
01/07/2018	C2930-04	35
01/07/2018	C2930-06	110
01/07/2018	C3011-04	30
01/07/2018	C3039-00	1790
03/05/2018	C3045-00	1
01/07/2018	C3045-00	15
11/05/2018	C3094-00	500
01/07/2018	C3094-00	90
07/05/2018	C4251-00	120
15/05/2018	C4251-00	108
15/05/2018	C4251-00	108
15/05/2018	C4251-00	64
01/07/2018	C4251-00	200
01/07/2018	C4251-00	410
07/05/2018	C4252-00	240
15/05/2018	C4252-00	560
01/07/2018	C4252-00	430
01/07/2018	C4252-00	480
01/07/2018	C4256-00	25

Inventory:

Item Code	Available Stock
C2798-00	1450
C2862-03	5439
C2930-01	1269
C2930-04	270
C2930-06	440
C3011-04	6
C3039-00	4856
C3045-00	195
C3094-00	140
C4251-00	799
C4252-00	738
C4256-00	181

[henrique0galli · Accepted solution]

SOLUTION:

I got a big help on this one, I couldn't solve by myself, however it works and it's great. Follow the steps below to have the outcome as designed above (excel print screen). Just a reminder, in order to have both tables, you can just copy and paste the ones previous given as code text (Inventory and Inbounds tables).

Step 1: Create a third table (this will be your calendar): it should refer to the Date columns from both tables (Inventory and Inbounds).

• Go to Modeling > New Table
• Type the function below

CalendarTable = CALENDAR(Min(Inbounds[DeliveryDate]),MAX(Inbounds[DeliveryDate]))

It should look like this:

Step 2: Create a Measure under Inbounds table:

MM = EOMONTH(LASTDATE(CalendarTable[Date]),0)

Something like this:

Step 3: create these two Measures under Calendar table:

• Right click on Calendar Table > New Measure

Measure Month:

Month = CalendarTable[Date].[Month]

Measure MonthNumber:

MonthNumber = CalendarTable[Date].[MonthNo]

Step 4: Create new Calculation under Inbounds table:

• Click on Modeling > New Measure

Lets call this function 'Balance'

Balance = 
VAR DD=EOMONTH(LASTDATE(CalendarTable[Date]),0)
VAR SumAllitemsinMonth=CALCULATE(SUM(Inbounds[Qty]),ALL(Inventory))
RETURN
IF(SumAllitemsinMonth=0,BLANK(),
 SUM(Inventory[AvailableStock])+ CALCULATE(sum(Inbounds[Qty]),FILTER(ALL(CalendarTable),CalendarTable[Date]<=DD)))

Step 5: Build a Matrix type layout and organise the fileds like this (below). - Also, don't forget to play around with the drill down button (highlighted yellow) so it gets to the layout you desire. In my case it was this one (same as I picture when I opened this question).

[henrique0galli]

UPDATE:

I managed to figure out the first step on Excel Pivot Tables - by creating a relationship between the tables. Now I need to create the calculated fields (or columns). I will post the progress later on. Hope it works then I can try to replicate this logic into Microsoft BI.

[v-yuta-msft]

Hi henrique0galli,

Which DAX formula are you using to calculate the Qty and have you merged the two tables?

Regards,

Jimmy Tao

[henrique0galli]

Hi @v-yuta-msft, I have just posted the solution with all details. Cheers