Running Total Filter - Need to Calculate All Sales for All Locations but to Filter to Items In Stock

[saralyndsay]

I am working on some weeks on hand reporting for Power BI. I am able to calculate Weeks on Hand for the total company easily, as well as by locations for all of our selling locations (stores). I am having some issues with how we want to handle our warehouse weeks on hand. The warehouse weeks on hand needs to review all sales qty for all locations for the skus that are stocked in the warehouse. I am able to accomplish this with the below measure, by using a calculated column I have created that has the items that are in stock at the current time: 

Avg Daily Sales II_D5 =

CALCULATE(

    DIVIDE(

        CALCULATE(SUM('Value Entry'[Item Ledger Entry Quantity]) * -1, 'Value Entry'[Item Ledger Entry Type],'Item'[D5 On Hand]<>0),

        CALCULATE(DISTINCTCOUNT('Calendar'[Date]))

    ),

    ALL(Locations[Code]),

    ALL(Locations[Name]),

    FILTER(

        ALL('Calendar'),

        'Calendar'[Date] >= EOMONTH(MAX('Calendar'[Date]), -4) && 'Calendar'[Date] <= MAX('Calendar'[Date])

    )

)

Column on the Item Table: 

D5 On Hand =

CALCULATE(

    SUM('Value Entry'[Item Ledger Entry Quantity]),

    'Value Entry'[Location Code] = "D5-MAIN",

filter

(all('Calendar'[Date]),'Calendar'[Date] >=date(2015,10,1)),

filter

(

all('Calendar'[Date]),'Calendar'[Date]<=max('Calendar'[Date]))

)

The issue I am trying to resolve, is that I can not plot this over time as the D5 On Hand is a static column. When I try to convert it to a measure, I run into issues. I need to calculate what items were in stock in D5 at the same end date as the period in the sales calculation. Since it is a running total of the value entry table, the filter should be applied for the running inventory as below for D5-MAIN only. But for the sales, the filter should be as in the above calculation for all locations. Any ideas? 

filter

(all('Calendar'[Date]),'Calendar'[Date] >=date(2015,10,1)),

filter

(

all('Calendar'[Date]),'Calendar'[Date]<=max('Calendar'[Date]))

) 

[amitchandak]

@saralyndsay , with help from date/calendar table

You need measure like

Inventory / OnHand =
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),filter(all(date),date[date] <=max(date[date]))) +
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=max(date[date])))

First part of Intial balance is option

Inventory / OnHand =
CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <=max(date[date])))

Begining of Period and end of period

Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw