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 cal...
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

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

      • amaniramahi's avatar
        amaniramahi
        Helper V
        7 years ago

        v-lid-msft  Thank you so much! it worked prefectly