How to calculate Weeks of Coverage for inventory (current SOH / next 3 months of sales)

[Cyriltra]

Hello,

I need to calculate the weeks of coverage (WoC) for each month in my table. The formula is : (inventory of the month) / ((sum of next 3 months)/13).

I have a pivot table like this:

	Feb	Mar	Apr	May	Jun	Jul
Sales	100	200	300	200	300	100
Inventory	1000	1500	900	2000	2500	1500
Weeks of Coverage

Feb WoC = (1000 / ((200+300+200)/13) = 18.6

Mar WoC = (1500 / ((300+200+300)/13) = 24.4

etc.

in my table, i use dimensions Month (text based) (Jan, Feb, Mar etc.). (I have another dimension for month in numeric: Month.nb (1,2,3,etc.) which is not in the dimensions of my table, it's a helper)

What should be the correct set expression to get what i need ?

For the moment, as a practice, I tried to simply get the sum of sales of the next 3 months, but it does not work. For each month (jan, feb, mar), I get the sales of that same month with the below expression:

Sum({$<[Year]={$(=Only([Year]))}, [Month.nb]={1,2,3}>}[WF SALES C US$])

(with above, i expected to get for each Month (jan to dec) the sum of sales of Month 1+2+3. But I get sales of month 1 for Jan, sales of month 2 for Feb, sales of Month 3 for March and no sales for the other months)

The selection is only the year and the region. There is no other selection related to the period or time.

Thank you

[sumanta1234 · Accepted solution]

Hi,

If the mentioned table is pivot table you can use the below expression to get the coverage:

SUM(Inventory)/((AFTER(SUM(Sales))+AFTER(SUM(Sales),2)+AFTER(SUM(Sales),3))/13)

[Cyriltra]

Thank you ! it does work in pivot table so I accept the solution.

I'm trying to do the exact same thing, but this time displaying as a bar chart, but it seems not working in this case.

Do  you have another trick up your sleeves ? 🙂

Thank you

edit: just realized the after() does not work for the last 3 months in my pivot table (Oct, Nov Dec) as there are not other value after them. This is ok if I checked current year. But I checked for a past year, I would like to see the correct WOC (but I don't want to display 2 years of data in the pivot table...