Qty monthly balance based on initial inventory (data provided)

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).