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

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])