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 Date ProductID 1/1/2020 PID234 1/1/2020 PID444 1/1/2020 PID657 1/2...
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])

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

  • PBI5851's avatar
    PBI5851
    Helper V
    6 years ago

    Hi,

     So using the mentioned time and under the assumption that the time slicer has been selected for 1/1/20 - 1/3/20 the calculation would be.. 

    PID234 was active for 3 days

    PID444 was active for 3 days

    PID657 was active for 2 days

    So the average active life length for that time period is 8 (total days each product was active) / 3 (total number of products within that time). So, the final value to be displayed in 2.6 days.

     

    Hope this helps.

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      6 years ago

      PBI5851 

      please try this

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