Count Days Between Two Dates in Same Column

[Anonymous]

I am building reports in powerbi using an established data model where the security in place prevents the ability to add columns; thus I must develop a measure to calculate the number of days between two dates within the same column (i.e., previous row). 

I have a table with a date field and I would like to calculate the number of days between each event (occurrence) (e.g., number of days between 03/08/2022 and 05/17/2022; number of days between 05/17/2022 and 06/06/2022, etc.).  Is there a way to do this by using a measure?  Any help/advice you can give would be very much appreciated.  

EventDate Field

03/08/2022

05/17/2022

06/06/2022

07/12/2022

09/29/2022

02/07/2023

02/08/2023

02/20/2023

[FreemanZ]

hi @Anonymous 

doable. would need the info of other columns to locate date rows. could you enrich your data sample?

[Anonymous]

Other columns, as a minimum, would include:

Unique Identifier Field (each record/row)

Eventdate (referenced originally)

Title Field

Event Summary Field

Responsible Department Field

[FreemanZ · Accepted solution]

hi @Anonymous 

Try to plot a table visual with UniqueIdentifier and a measure like:

daysbetween =

VAR _date = MAX(TableName[EventDate])

VAR _datepre =

MAXX(

   FILTER(

      ALL(TableName[EventDate]),

      TableName[EventDate]<_date

   ),

  TableName[EventDate]

)

RETURN

DATEDIFF(_datepre, _date, DAY)

[Anonymous]

Thank you!  I will try this!

[FreemanZ]

hi @Anonymous 

and the criteria to pick dates for duration calculation?

[Anonymous]

To include on a visual whether table, combo graph (bar (event) line (days between events)