topic Re: Calculate difference in time between dates in same column in DAX Commands and Tips

Calculate difference in time between dates in same column

Anonymous — Fri, 17 Feb 2023 18:43:20 GMT

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!

Re: Calculate difference in time between dates in same column

OwenAuger — Sat, 18 Feb 2023 01:10:06 GMT

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

Re: Calculate difference in time between dates in same column

FreemanZ — Sat, 18 Feb 2023 06:09:11 GMT

hi Anonymous

do you have other columns?

Re: Calculate difference in time between dates in same column

Anonymous — Sat, 18 Feb 2023 12:36:12 GMT

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!

Re: Calculate difference in time between dates in same column

tamerj1 — Sat, 18 Feb 2023 13:54:52 GMT

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