How to Calculate Inventory Days Using DAX

[Anonymous]

Hi,

I am new to using Power BI and DAX functions. Appreciate if anyone could help me with this.

I want to calculate the Inventory Day of a list of materials (also categorise by MRP) based on the Inventory value captured at month end closing. For example, in end of May'21, the sum of Inventory value under category 1RL is $3170. I have the COGS record for the last 6 months (posting period 12, 1.. 5). So the calculation of Inventory Days = ($3170-$1451-$1489)/$1743*31days+30days+31days = 65 days.

This is my Inventory Table and COGS Table in power BI.

I need the Inventory Days for each material as well as each MRP.

Appreciate any help, thanks in advance !

[Anonymous]

Hi @amitchandak , first of all thanks for the help, much appreciated !

The purpose of calculating the Inventory Days is to understand how many days of stocks we are holding versus the previous months COGS.  As you can see below, the coverage of the Inventory could varies instead of a fix 3 months. Example, for 1RL inventory it covers about 2 months+ (65 Days), for 4RL inventory it covers about 5 months (150 Days).

I have added a Date table in my power BI and an example file link attached. I would love for your help here with the formula. Thanks once again.

PBIX file: https://drive.google.com/file/d/1doO1tsVJas4WinqOevgF80dOxQkMxNUt/view?usp=sharing 

[amitchandak]

@Anonymous , I am not able to get you formula completely. So I am giving measures you may need to get that with help from date table and time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,MONTH))

Days of last three month = day(eomonth(today(),0)) + day(eomonth(today(),-1)) + day(eomonth(today(),-2))