Calculate average active length

[PBI5851]

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/2020	PID234
1/2/2020	PID444
1/3/2020	PID444
1/3/2020	PID234
1/3/2020	PID657
1/4/2020	PID657
1/5/2020	PID234

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. 

[ryan_mayu · Accepted solution]

@PBI5851 

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

[ryan_mayu · Accepted solution]

@PBI5851 

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

[PBI5851]

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]

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]

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 · Accepted solution]

@PBI5851 

please try this

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

[Anonymous]

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