Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to Calculate Inventory Days Using DAX

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 !

 

 

 

 

 

 

 

 

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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