Calculating days between first and last order by product

[xonder]

Hi There,

I have a data set that includes vhcid(Vehicle ID) and data_date.

In the matrix below, I would like to calculate the number of days a vehicle has been online. Below, for example, vehivle 7985400 has been online since 5th October until the 23rd October, or 18 days.

I tried the follwoing DAX:

days_online = 1*(MAX(Downloads[data_date])-MIN(Downloads[data_date]))

As you see it returns an odd result of 60 days for all lines. This actually corresponds to the difference between the first and last dates in my entier data set. Not for a specific vhcid as I would like.

The FIRSTDATE and LASTDATE DAX functions did not yield much either.

I would like to return a table with [vhcid] and [days_online]

Thanks for your help!!

[amitchandak · Accepted solution]

@xonder , try like

days_online = calculate(datediff(MIN(Downloads[data_date]),MAX(Downloads[data_date]),day), allexcpet(Downloads, Downloads[vchid]))

[xonder]

Thanks, amitchandak. Works!

The solution works provided I summerize as average.

Never thought such a simple idea would need such a complex formula.