To calculate the numerator, we want to calculate the average value or units of inventory at the beginning and end of our period (week) and divide this by 2. Since our data specifies the ending inventory for each week in the Ending Inventory column, we simply store the value of this column for the current row in the __EndingInventory variable. To find our beginning inventory, __BeginningInventory, we store the value of the Ending Inventory column for the previous week in the __BeginningInventoryTemp variable. This is done by using ALL to break out of row context and then by using FILTER to return the previous row. Since our data is weekly, we know that the previous row has a Week column value that is our current Week column value (EARLIER) minus 7 days. Once we have filtered down to a single row, we can use MAXX or really any other similar function to return the value of the Ending Inventory column. Then, we can do a quick check to account for the beginning of the table where we have no previous week's inventory data. This is done by using an IF statement and checking if __BeginningInventoryTemp is blank (ISBLANK). If __BeginningInventoryTemp is blank, we know that we are at the beginning of our table and thus we can estimate the beginning inventory for the week by adding the Demand column to our __EndingInventory. Otherwise, we simply assign the value of __BeginningInventoryTemp.
Now, we can calculate the __AverageInventory variable by adding __EndingInventory and __BeginningInventory and then dividing by 2. We can also calculate the average amount of product sold or otherwise taken out of stock per week, __AverageDemand, by simply getting the average of the Demand column for ALL of our rows using the AVERAGEX function. Finally, we can RETURN a value for DoS by simply dividing __AverageInventory by __AverageDemand and multiplying by 7, since there are seven days in a week.