Days of Inventory

[Anonymous]

I am working toward converting several Excel spreadsheets to Power BI reports. One of these reports includes a days of inventory calculation (DOI). I cannot for the life of me figure out how to perform this calculation. It needs to take next period’s forecasted sales and evaluate whether that will be greater than current month's inventory. If it is less than current month’s inventory then you add the following month's forecasted sales, to the point that when the sum of forecasted sales for n periods is greater than inventory, that number is divided by the current month’s inventory and then the quotient is divided again by the number of selling days for the current month.

A simplified version in excel it looks like this:

=C15/(D3+/T3)

Where:

C15 = Current month's inventory

D3 = Next month's forecasted sales

T3 = Next month's number of selling days

The problem that I am having is I cannot figure out how to plot this on a graph with months as the x axis. So the value displayed is the DOI for each month using next months sales forecast. I have tried making measures for the variables outlined above and tried to use Dateadd and NextMonth, to offset the months for sale forecast but it does not work. They look like this:

Inventory: EndInv Month 1 = CALCULATE(SUM(Inventory_2018_Actual[2018 EndInv Actual BBLs]))

Next month’s Sales Forecast: 2018 Month 1 Forecast = CALCULATE(SUM('2018ACT_STR'[2018ACT_STR]), DATEADD(CAL_DT[CURR_MO_FIRST_DT], 1 , MONTH))

Selling Days: I am hardcoding these at this point. I will need to figure out how to make this change dynamically with the month.

This is my DOI Equation so far:

DOI Final =

var EndInv = 'Inventory_2018_Actual'[EndInv]

var Forecast1 = '2018ACT_STR'[2018 Month 1 Forecast]

var Forecast2 = '2018ACT_STR'[2018 Month 2 Forecast]

Return

IF(EndInv <= Forecast1, DIVIDE(EndInv1,Forecast1,0)*23,

   IF(EndInv1 <= Forecast1 + Forecast2, DIVIDE([EndingInv 1]- Forecast1, Forecast2,0)*23))

PLZ HELP!!!

[Iamnvt]

hi,

Could you post PBI file with a sample, or dummy data?

and could you explain the expected output again?

"The problem that I am having is I cannot figure out how to plot this on a graph with months as the x axis"

[Anonymous]

I don't think I can post an example becuase there is so much data, but I can try to clarify.

I have the below equation; And I am trying to get this to calculation DOI for every month.

The way it is written right now it is anchored to January as EndInv Month 1 becuase that is the only way I can get it to work. I need it to be able to be dynamic, so that for each month of the year the formula will look 1 month forward to get Sales Forecast and Use the current month's inventory number in the calculation . Does that help clarify? 

Additional issue, When I go to use my filters the vizualiation just disappears? So, slicers are also not working and I am not sure why...?

Where :

2018 Month 1 Forecast = CALCULATE(SUM('2018ACT_STR'[2018ACT_STR]), DATEADD(CAL_DT[CURR_MO_FIRST_DT], 1 , MONTH))

2018 Month 2 Forecast = CALCULATE(SUM('2018ACT_STR'[2018ACT_STR]), DATEADD(CAL_DT[CURR_MO_FIRST_DT], 2, MONTH))

EndInv Month 1 = CALCULATE(SUM(Inventory_2018_Actual[2018 EndInv Actual BBLs]), CAL_DT[CAL_YR_MO_NBR] = 201801)

DOI Final =

var EndInv1 = 'Inventory_2018_Actual'[EndInv Month 1]

var Forecast1 = '2018ACT_STR'[2018 Month 1 Forecast]

var Forecast2 = '2018ACT_STR'[2018 Month 2 Forecast]

Return

IF(EndInv1 <= Forecast1, DIVIDE(EndInv1,Forecast1,0)*23,

    IF(EndInv1 <= Forecast1 + Forecast2, DIVIDE([EndingInv 1], Forecast2,0)*23))