Forum Discussion

ToddMate's avatar
ToddMate
Helper II
7 years ago
Solved

Calculate Difference Between Dates in Same Column

Hi All,

 

I need help in determining the difference between two dates that reside in the same column on seperate rows in the same table. I have a Sales date on one row and a Connected date on another row, the table is called TransactionsFACT.

 

In the example i would expect to see 5 days as the answer. (15/04/2019 - 10/04/2019)

 

These share a unique combination of Account Number and Service Number as per the screenshot. (excel screenshot)

 

The bigger picture here is that i would like to apply this across all entries in the table and store this in a calculated column for use in further analysis.

 

Is anyone able to assist me.

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    This calculated column formula works

    =if(Table1[transaction Type]="Connected",Table1[Date of Transaction]-CALCULATE(MAX(Table1[Date of Transaction]),FILTER(Table1,Table1[Account Number]=EARLIER(Table1[Account Number])&&Table1[Service Number]=EARLIER(Table1[Service Number])&&Table1[transaction Type]="Sale")),BLANK())

    Hope this helps.

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    ToddMate try this measure

     

    # Days = 
VAR __salesDate = CALCULATE( MAX( Table1[TransactionDate] ), 
    ALLEXCEPT( Table1, Table1[AccountNumber], Table1[TransactionType] ), 
    Table1[TransactionType] = "Sale" )
VAR __connectedDate = CALCULATE( MAX( Table1[TransactionDate] ), 
    ALLEXCEPT( Table1, Table1[AccountNumber], Table1[TransactionType] ), 
    Table1[TransactionType] = "Connected" )
RETURN DATEDIFF( __salesDate, __connectedDate, DAY )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    Against one sale date would there be only one connected date for that Account and Service Number?  If not, then which dates should be subtracted?  Please show thr expected result in Column E.

    • ToddMate's avatar
      ToddMate
      Helper II
      7 years ago

      Ashish_Mathur Yes that is correct, there can ever only be one sales date and one connected date per combination of Account & Service. (In this case Account Number 1234 and Service Number 555)

       

      Column E, Row 4 would simply contain '5' representing the 5 days between 10/04/2019 & 15/04/2019

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

        This calculated column formula works

        =if(Table1[transaction Type]="Connected",Table1[Date of Transaction]-CALCULATE(MAX(Table1[Date of Transaction]),FILTER(Table1,Table1[Account Number]=EARLIER(Table1[Account Number])&&Table1[Service Number]=EARLIER(Table1[Service Number])&&Table1[transaction Type]="Sale")),BLANK())

        Hope this helps.