Calculate Difference Between Dates in Same Column

[ToddMate]

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.

[Ashish_Mathur]

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]

@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 · Accepted solution]

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.

[parry2k]

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