Calculate Opening Inventory (Help Urgent)

Question

Hi All,&nbsp;I am currently calculating inventory days number. I do have data example as shown below&nbsp;JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECClosing Stock 2020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,704&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,485&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,294&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,175&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,988&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19,687&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,944&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,491&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,155&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,818&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,944&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,407Opening Stock 2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,407&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,706&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,293&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,189&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,656&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,269&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,863&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,588&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,417&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,244&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,109&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,514Closing Stock 2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,518&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,581&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,445&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21,653&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,450&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19,322&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,665&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,142&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,617&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,042&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,268&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,312Sales 2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,587&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,071&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,578&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,479&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,824&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 16,406&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,120&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,909&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,796&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10,681&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,454&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,888&nbsp;The question is, how to calculate inventory days if the formula is ((Opening Stock MTD + Opening Stock last Month) / 2) / Sales MTD * 30 particulary formula required if I would like to calculate inventory days for Jan 2021.&nbsp;Based on scenario above, can i still use slicer with range for month?&nbsp;Would highly appreciate for all the feedback, thank you

anonymous · Accepted Answer

Hi&nbsp; Anonymous&nbsp;&nbsp;,
Here are the steps you can follow：
1. Enter the Power query through Transform data, select the [Status] column, and select Transform – Unpivot Columns – Unpivot Other Colum.

2. In the Power query, select Add Column – Index Column – From 1.

3. Create calculated column.
Column =
RANKX(FILTER(ALL('Table'),'Table'[Status]=EARLIER('Table'[Status])),'Table'[Index],,ASC)
Result：

4. Create measure.
Opening Stock MTD =
var _select=SELECTEDVALUE('Table'[Attribute])
var _co=CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'),'Table'[Attribute]=_select&amp;&amp;'Table'[Status]="Opening Stock 2021"))
var _today =MONTH(TODAY())
var _OpeningStockMTD=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Status]="Opening Stock 2021"&amp;&amp;'Table'[Column]&gt;=_co&amp;&amp;'Table'[Column]&lt;=_today))
return
_OpeningStockMTDOpening Stock last Month =
var _select=SELECTEDVALUE('Table'[Attribute])
var _co=CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'),'Table'[Attribute]=_select&amp;&amp;'Table'[Status]="Opening Stock 2021"))
return
IF(_co=1,0,
    CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Column]=_co-1&amp;&amp;'Table'[Status]="Opening Stock 2021")))Sales MTD =
var _select=SELECTEDVALUE('Table'[Attribute])
var _co=CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'),'Table'[Attribute]=_select&amp;&amp;'Table'[Status]="Sales 2021"))
var _today =MONTH(TODAY())
var _SalesMTD=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Status]="Sales 2021"&amp;&amp;'Table'[Column]&gt;=_co&amp;&amp;'Table'[Column]&lt;=_today))
return
_SalesMTDinventory days = (([Opening Stock MTD]+[Opening Stock last Month])/2)/[Sales MTD] *30
5. Use the card for the created meausure and place [Attribut] in the slicer.
6. Result：
Select [Attribut]=JAN

&nbsp;
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Calculate Opening Inventory (Help Urgent)

1 Reply

Related content

Inventory and Stock balance calculation

Inventory opening closing

Closing and opening inventory calculation

Calculate a Running Daily Inventory

Opening Qty calculation

	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
Closing Stock 2020	18,704	14,485	16,294	16,175	18,988	19,687	16,944	15,491	14,155	12,818	14,944	16,407
Opening Stock 2021	16,407	12,706	14,293	14,189	16,656	17,269	14,863	13,588	12,417	11,244	13,109	12,514
Closing Stock 2021	16,518	18,581	18,445	21,653	22,450	19,322	17,665	16,142	14,617	17,042	16,268	1,312
Sales 2021	15,587	12,071	13,578	13,479	15,824	16,406	14,120	12,909	11,796	10,681	12,454	11,888