Help with Date Difference with Filters

[SpencerRTT]

Hi All

I'm looking to create a column in my table that calculates the age of a tyre by using DATEDIFF with filters included so it can determine the age of a particular tyre based on the car make and location. A sample dataset would look like one below, but would like the tyre age to calculate number of days since it was last replaced based on its make and location. Ideally i wouldn't use an IF statement because the full dataset includes many car makes amongst other variables.

Thanks!

CAR	LOCATION	DATE REPLACED	TYRE AGE
Toyota	Front Left	1/06/2015
Ford	Front Right	3/08/2015
Mazda	Back Left	16/08/2015
Toyota	Back Right	5/09/2015
Ford	Front Left	14/09/2015
Mazda	Front Right	16/09/2015
Toyota	Back Left	5/10/2015
Ford	Back Right	10/10/2015
Mazda	Front Left	1/11/2015
Toyota	Front Right	8/11/2015
Ford	Back Left	12/11/2015
Mazda	Back Right	25/12/2015
Toyota	Front Left	1/01/2016
Ford	Front Right	8/01/2016
Mazda	Back Left	15/01/2016
Toyota	Back Right	22/01/2016
Ford	Front Left	1/02/2016
Mazda	Front Right	8/02/2016
Toyota	Back Left	15/02/2016
Ford	Back Right	22/02/2016

[Anonymous]

Hi, 

You can create new column using following DAX

TYRE AGE 1 = DATEDIFF('CAR data'[DATE REPLACED],TODAY(),YEAR)

Here, I have used interval as YEAR you can use day,week,month according to your requirement

Hope this works!!

Thanks

[SpencerRTT]

Hi All, thanks for the replies. Apologies for not being too clear in the question, what i'm looking for is the age of the tyre between replacements. ie, Toyota front left was replaced on 1/06/2015 and then again on 01/01/2016, so the age corresponding to the row on 01/01/2016 will be the date difference between these dates, else an age to the present date.

Any ideas?

Thanks

Spencer.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Data[DATE REPLACED]),FILTER(Data,Data[CAR]=EARLIER(Data[CAR])&&Data[LOCATION]=EARLIER(Data[LOCATION])&&Data[DATE REPLACED]<EARLIER(Data[DATE REPLACED])))),today()-Data[DATE REPLACED],Data[DATE REPLACED]-CALCULATE(MAX(Data[DATE REPLACED]),FILTER(Data,Data[CAR]=EARLIER(Data[CAR])&&Data[LOCATION]=EARLIER(Data[LOCATION])&&Data[DATE REPLACED]<EARLIER(Data[DATE REPLACED]))))

Hope this helps.

[SpencerRTT]

Thanks, this worked well. Much appreciated.

[TomMartens]

Hey @SpencerRTT , 

I'm wondering if this DAX statement creating a calculated column provides what you are looking for:

Column = DATEDIFF('Table'[DATE REPLACED] , TODAY() , DAY)

As always with calculations around timespans, you have to consider, if the result is correct.

The result of the above formula will return 1 for a tire that has been replaced yesterday, but maybe you want to add 1 as it also could be possible to consider the tire has been used for 2 days already. Sometimes this little difference can become important.

Regards,

Tom

[amitchandak]

@SpencerRTT 

Not sure I got it completely

You can get a new Column

New column = datediff([	DATE REPLACED],today(),DAY)

Or a measure like

sumx(summarize(table,table[CAR],table[LOCATION],"diff", datediff(min(Table[DATE REPLACED]),today(),DAY)),[diff])