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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    SpencerRTT
    Frequent Visitor
    6 years ago

    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's avatar
      Ashish_Mathur
      Super User
      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.

       

      • SpencerRTT's avatar
        SpencerRTT
        Frequent Visitor
        6 years ago

        Thanks, this worked well. Much appreciated.