Forum Discussion

amaniramahi's avatar
amaniramahi
Helper V
7 years ago
Solved

Average Stock days

Hi,

 

I have two tables, the first one contains SKUs and the order no. for each one.

and the other table contains the order no. and receiving date for each order no.

 

I created a measure to calculate the stocking days for each order no.

 

now i need to cauculate the average stocking days for my SKUs.

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    Hi amaniramahi ,

     

    Firstly you can use calculate column to calculate the days in stock for each order

     

    days in stock =
DATEDIFF ( Receiving[Receive Date], TODAY (), DAY )

     

    Then we can use create this measure to meet your requirement.

     

    average-stock-days =
AVERAGEX (
    Stock,
    LOOKUPVALUE ( Receiving[days in stock], Receiving[Order], [Order No.] )
)

     

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ DongLi
    If this post helps, then please consider Accept it as the solution to help the other members find it more

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    7 years ago

    Hi amaniramahi,

     

    If you have relationship between two tables based on the No, you can simply creating such measure to calculate average day for SKUs.

     

    Measure = AVERAGE(Table2[Receiving Date])

     

    If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    BTW, pbix as attached.

     

    Community Support Team _ DongLi
    If this post helps, then please consider Accept it as the solution to help the other members find it more

    • amaniramahi's avatar
      amaniramahi
      Helper V
      7 years ago

      Thank you v-lid-msft  for the reply.

       

      sorry for not clarifying it clear.

       

      first of all, indirect relationship between Stock and recieving through orders table (the key between them is order no.)

       

       

      the first table "Stock" contains the following information

      my SKU and the purchasing order for it

       

       


       

      the second table "receiving" contains the following information

       

       

      I have created a measure to calculate the days in stock for the SKUs ordered in each purchase order

       

      days in stock = if(Receiving[Receiving Date].[Date] = BLANK(),BLANK(),today()-Receiving[Receiving Date].[Date])

       

      now i want to calculate the average days in stock for my SKUs, the desired result for the example above shall be 384.56 

       

       

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        7 years ago

        Hi amaniramahi ,

         

        Firstly you can use calculate column to calculate the days in stock for each order

         

        days in stock =
DATEDIFF ( Receiving[Receive Date], TODAY (), DAY )

         

        Then we can use create this measure to meet your requirement.

         

        average-stock-days =
AVERAGEX (
    Stock,
    LOOKUPVALUE ( Receiving[days in stock], Receiving[Order], [Order No.] )
)

         

        BTW, pbix as attached.

         

        Best regards,

         

        Community Support Team _ DongLi
        If this post helps, then please consider Accept it as the solution to help the other members find it more