Calculate difference in time between dates in same column

[Anonymous]

Hi all,

I have the following column, and I wish to calculate the difference between each row - ie the length of time between Row 2 (20/08/18) and Row 1 (18/01/2017).

Any ideas on how to effectively do this?

Thanks!

[tamerj1]

Hi @Anonymous 

Please try

Days Difference =
VAR SelectedDates =
ALLSELECTED ( 'Table'[Date] )
RETURN
SUMX (
VALUES ( 'Table'[Date] ),
VAR CurrentDate = 'Table'[Date]
VAR FilteredDates =
FILTER ( SelectedDates, 'Table'[Date] < CurrentDate )
VAR PreviousDate =
MAXX ( FilteredDates, 'Table'[Date] )
RETURN
IF ( PreviousDate <> BLANK (), INT ( CurrentDate - PreviousDate ) )
)

[Anonymous]

My apologies, the dates should have been in ascending order as in the screenshot below now. There are other rows that I'd rather not show if possible for privacy reasons (ie work related), but the dates should be the column by which the table is sorted.

And to answer your other question, a calculated column would be the ideal scenario.

Thank you for your help so far!

[FreemanZ]

hi @Anonymous 

do you have other columns?

[OwenAuger]

Hi @Anonymous 

The OFFSET function would work well here.

To come up with the exact DAX expression, we would need to clarify:

• What determines the sort order? I can see the dates in your image are not in ascending/descending order. Is there another column partitioning or defining the sort order?
• Do you want a measure (which would respond to filters) or a calculated column?
  In the case of a measure, we would need to clearly define which dates are included, for example it could be all dates visible in the filter context, or all dates for which certain measure(s) are nonblank.

Regards,

Owen