Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Help

Needs to calculate Last 12 months average inventory

 

Days of Inventory in Hand (DIH) =  Average Inventory / Average daily Net Sales.

 

Now how to arrive average inventory:

 

From the selected date by user say 04.06.2020, go backward and check inventory value on 04.06.20, 04.05.20…………….. 04.07.19 (12 months)

Add all twelve months inventory and divide it by 12 so that you will get average inventory.

 

Average daily net sales =  say selected date 04.06.20 then ( Net sales from 04.07.19 to 04.06.20 (12 months) divide it by 12)              

 

Guys please help on this

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi,

     

    Did you have a calendar table with relationship with your fact table?

     

    You can use the following dax to calculted rolling average inventory for last 12 months:

     

    measure =
VAR _DATE =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Inventory] ),
        DATESBETWEEN ( 'Date'[Date], DATEADD ( _DATE, -12, MONTH ), _DATE )
    ) / 12

     

    It will be the same for Net Sales. Or you can use quick measure in power bi to calculate rolling average. Please refer to https://www.mssqltips.com/sqlservertip/5635/creating-a-rolling-average-graph-with-power-bi/.

     

    It will be nice if you can show us some sample data by onedrive for business.

     

    Best Regards,

    Dedmon Dai

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , with a date calendar

     


    Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
    Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

     

    Or First Sum at month level and then take Avg

     

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

    Avg Rolling 12 = AverageX(summarize(Sales,Date[Month Year],"_1",[Rolling 12]),[_1])

    Or [Rolling 12]/12

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      In my case, the user will select one date, and I have to average inventory based on that date.

      Days of Inventory in Hand (DIH) =  Average Inventory / Average daily Net Sales.

       

      Now how to arrive average inventory:

       

      From the selected date by user say 04.06.2020, go backward and check inventory value on 04.06.20, 04.05.20…………….. 04.07.19 (12 months)

      Add all twelve months inventory and divide it by 12 so that you will get average inventory.

       

      Average daily net sales =  say selected date 04.06.20 then ( Net sales from 04.07.19 to 04.06.20 (12 months) divide it by 12)              

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    6 years ago

    Hi,

     

    Did you have a calendar table with relationship with your fact table?

     

    You can use the following dax to calculted rolling average inventory for last 12 months:

     

    measure =
VAR _DATE =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Inventory] ),
        DATESBETWEEN ( 'Date'[Date], DATEADD ( _DATE, -12, MONTH ), _DATE )
    ) / 12

     

    It will be the same for Net Sales. Or you can use quick measure in power bi to calculate rolling average. Please refer to https://www.mssqltips.com/sqlservertip/5635/creating-a-rolling-average-graph-with-power-bi/.

     

    It will be nice if you can show us some sample data by onedrive for business.

     

    Best Regards,

    Dedmon Dai

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      But my client is asking average inventory calculation like below

       

      From the selected date by user say 04.06.2020,

      go backward and check inventory value on 04.06.20, 04.05.20…………….. 04.07.19 (12 months)

      Add all twelve months inventory and divide it by 12 so that you will get average inventory.

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Please see my edited reply.

         

        Best Regards,

        Dedmon Dai