Forum Discussion

PBI5851's avatar
PBI5851
Helper V
6 years ago
Solved

Calculate average active length

Hello,

I have a table that captures for every day each and every product that is active. 

Ex: 

Date ProductID

DateProductID
1/1/2020PID234
1/1/2020PID444
1/1/2020PID657
1/2/2020PID234
1/2/2020PID444
1/3/2020PID444
1/3/2020PID234
1/3/2020PID657
1/4/2020PID657
1/5/2020PID234

 

So when i have to provide how many Products were active for a given day, i do a count of ProductID for a specific day. Using above table, PID657 was active on 1st, 3rd and 4th, so products can go in and out. But the request is to capture what is the average that a Product has been active. I'm not sure on how exactly I can achieve that. Any help please. 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    PBI5851 

     

    not very clear about your request. Could you please share the expected output for your sample data?

  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    PBI5851 

    please try this

    average = 
COUNTROWS('dateproductid')/DISTINCTCOUNT(dateproductid[ProductID])

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I do something very similar with inventory, I would assume it should work the same.  Basically I want to take the average inventory of each day.

     

    So I do

    AVERAGEX( VALUES ( Dates[Date]), [count you want to average] )

    This will average it over the relevant filter datte range

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    PBI5851 

     

    not very clear about your request. Could you please share the expected output for your sample data?

    • PBI5851's avatar
      PBI5851
      Helper V
      6 years ago

      Not very strong on my math skills 🙂 . But basically what i intend to capture is, within the date period of the date slicer that i have for the visual, how many months has each Product been on the table, divide by the total number of products within that date range as per the slicer. 

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

        Hi PBI5851 ,

         

        It 's still not clear for me. Would you please tell us what your expected output based on the sample data?

         

        Best Regards,

        Dedmon Dai