Calculate Order Book / Inventory

[Anonymous]

Hello,

Im trying to create a report that shows the current (monthly) order book (inventory). I have two tables, one for Intake and one table for delivery. These two I have combined to one (table 3), in this one I would like to show the inventory monthly. How should the formula be constructed correctly? 

 Thank you!

Edit:  See pictures from Power BI. 

Final result I want: 

[Anonymous]

Hi @Anonymous,

I'd like to suggest you do 'unpivot columns' on your value fields to convert them to attribute and value.

Unpivot columns (Power Query) 

Then you can simply use current row contents attribute and value to lookup value and calculate between tables.

Regards,

Xiaoxin Sheng

[amitchandak]

You have create Formula

Inventory = Cumulative Intake - Cumulative delivery

Inventory = CALCULATE(SUM(Intake[Intake]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(delivery[delivery]),filter(date,date[date] <=maxx(date,date[date])))

USe common date calendar

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Hello @amitchandak ,

Thank you for the reply, some additional questions for me. Right now I have a date calendar in my table 3, where I want to create this inventory. In the same table I have all intake and delivery. 

So the formula should be for lets say Tractor Inventory: 

Inventory Tractor = CALCULATE(SUM(Table3[Tractor Intake]),filter(Table3,table3[date] <=maxx(tTable3,table3[date]))) - CALCULATE(SUM(Table3[Tractor delivery]),filter(Table3,table3[date] <=maxx(Table3,table3[date])))

Is this correctly understood from me? Or should I have one Date table and then "retrieve" the numbers directly from my Table 1 (Delivery) and table 2 (intake). Right now I have moved everything to Table 3 that I need by the formula RELATED..

Thanks!