Forum Discussion

SpencerRTT's avatar
SpencerRTT
Frequent Visitor
6 years ago
Solved

Help with Date Difference with Filters

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

     

TomMartens's avatar
TomMartens
Super User
6 years ago

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