Forum Discussion

BigMac's avatar
BigMac
Regular Visitor
3 years ago
Solved

Inventory on hand measures

Hi,

 

I am looking for explanation how to, and why that way (step by step - to understand) create measure to calculate actual inventory day by day.

 

I would like from table that takes today's morning real inventory, and calculate day by:

After demand = Start inventory - Demand = After demand

End inventory = Start inventory - Demand + Supply = End inventory

 

The requested result should look like that:

 

PeriodStart inventoryDemandAfter demandSupplyEnd Inventory
10.10.2022800100700100800
11.10.2022800180620200820
12.10.202282090730100830
13.10.202283080750100850
14.10.2022850170680200880
Week 42880880010001000
Week 4310004405605001060

 

My tables below:

 

Today inventory:

DateStart inventory
11.10.22800

 

Calendar table:

DateYearQuarterMonthWeekWeekday
11.10.222022410412
12.10.222022410413
13.10.222022410414
14.10.222022410415

 

Supply table:

DateSupply
10.10.22100
11.10.22200
12.10.22100
13.10.22100
14.10.22200

 

Demand table:

DateDemand
10.10.22100
11.10.22180
12.10.2290
13.10.2280
14.10.22170

 

Actually, Today inventory table is refreshing based on Today(), so the calculactions should also start on the date given by that date.

 

My final idea is to see supply and demand up to the end of the next month from today date. The tables are actually refreshing like that but I shortened them to 2 and a half of the week to make it simpler.

Need Help
Tutorial Requests
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    After demand =
VAR StartDate =
    CALCULATE ( MAX ( 'Today inventory'[Date] ), ALL () )
VAR EndDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Today inventory'[Start inventory] ) - SUM ( Demand[Demand] ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
RETURN
    Result

    First you need to get the start date from your starting inventory table, and the end date is the last date which is visible in the current filter context, so MAX('Date'[Date]) will retrieve that.

    Then you just need to total up all the individual columns for all rows which fall between those 2 dates.

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    After demand =
VAR StartDate =
    CALCULATE ( MAX ( 'Today inventory'[Date] ), ALL () )
VAR EndDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Today inventory'[Start inventory] ) - SUM ( Demand[Demand] ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
RETURN
    Result

    First you need to get the start date from your starting inventory table, and the end date is the last date which is visible in the current filter context, so MAX('Date'[Date]) will retrieve that.

    Then you just need to total up all the individual columns for all rows which fall between those 2 dates.