Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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 |
Solved! Go to 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.
Hi,
You can create new column using following DAX
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.
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.
Thanks, this worked well. Much appreciated.
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
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])
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.