Average number of days before item is sold

[xonder]

I collect data of advertised vehicles at irregulars intervals. Each time I collect data, I get a snapshot of what is currently being advertised along with a time stamp.

By subtracting the last date and the first at which a vehicle was advertised, I get the average number of days a given vehicle has been advertised.

days_online = CALCULATE(DATEDIFF(MIN(azw[date]),MAX(azw[date]),DAY),ALLEXCEPT(azw,azw[ID]))

How do I filter this for only the vehicles that have already been sold (no longer being advertised), ie. the last date an vehicle ID appears is smaller than TODAY?

From this I would also like to calculate how many vehicles have been sold since I started collecting data.

Here is the file: pbix file for reference 

Thanks for your help.

[AlB · Accepted solution]

@xonder 

If a vehicle appears on 05-11-2020 and on 06-11-2020, is that two days or one day being advertised? If it's two days, which is probably the most reasonable, you can just add a one to the DATEDIFF in both measures and the problem you just described will also be solved.

Days online V2 =
AVERAGEX (
    DISTINCT ( azw[ID] ),
    CALCULATE ( DATEDIFF ( MIN ( azw[date] ), MAX ( azw[date] ), DAY ) ) + 1
)

and then for the vehicles sold:

Days online vehicles sold =
AVERAGEX (
    FILTER ( DISTINCT ( azw[ID] ), CALCULATE ( MAX ( azw[date] ) ) < TODAY () ),
    CALCULATE ( DATEDIFF ( MIN ( azw[date] ), MAX ( azw[date] ), DAY ) ) + 1
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[xonder]

Thanks @AlB . This works for all entries except for those that have a single data point.

Say I collected data on the 05.11.2020 and on the 25.11.2020. ID 123456 does not appear in the data from 05.11.2020 but does appear in the data collected on the 25.11.2020. With the measure you suggested I get value of 0.

How would I have to tweak the measure to get a value of 1?

Thanks

[AlB]

Hi @xonder 

I believe the way you are calculating the average number of days vehicles have been advertsed is incorrect. By taking the average of the calculated column you've created, you're giving more weight to the vehicles that appear more times (in more rows) in the data. Try this instead:

Days online V2 =
AVERAGEX (
    DISTINCT ( azw[ID] ),
    CALCULATE ( DATEDIFF ( MIN ( azw[date] ), MAX ( azw[date] ), DAY ) )
)

and then for the vehicles sold:

Days online vehicles sold =
AVERAGEX (
    FILTER ( DISTINCT ( azw[ID] ), CALCULATE ( MAX ( azw[date] ) ) < TODAY () ),
    CALCULATE ( DATEDIFF ( MIN ( azw[date] ), MAX ( azw[date] ), DAY ) )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers