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 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!

 

CARLOCATIONDATE REPLACEDTYRE AGE
ToyotaFront Left1/06/2015 
FordFront Right3/08/2015 
MazdaBack Left16/08/2015 
ToyotaBack Right5/09/2015 
FordFront Left14/09/2015 
MazdaFront Right16/09/2015 
ToyotaBack Left5/10/2015 
FordBack Right10/10/2015 
MazdaFront Left1/11/2015 
ToyotaFront Right8/11/2015 
FordBack Left12/11/2015 
MazdaBack Right25/12/2015 
ToyotaFront Left1/01/2016 
FordFront Right8/01/2016 
MazdaBack Left15/01/2016 
ToyotaBack Right22/01/2016 
FordFront Left1/02/2016 
MazdaFront Right8/02/2016 
ToyotaBack Left15/02/2016 
FordBack Right22/02/2016 

 

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.

     

6 Replies

  • 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.

         

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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])

     

     

     

  • 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