topic Count Days Between Two Dates in Same Column in DAX Commands and Tips

Count Days Between Two Dates in Same Column

Anonymous — Thu, 09 Mar 2023 19:21:44 GMT

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

Re: Count Days Between Two Dates in Same Column

FreemanZ — Thu, 09 Mar 2023 19:25:54 GMT

hi Anonymous

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

Re: Count Days Between Two Dates in Same Column

Anonymous — Thu, 09 Mar 2023 19:30:58 GMT

Other columns, as a minimum, would include:

Unique Identifier Field (each record/row)

Eventdate (referenced originally)

Title Field

Event Summary Field

Responsible Department Field

Re: Count Days Between Two Dates in Same Column

FreemanZ — Thu, 09 Mar 2023 19:52:02 GMT

hi Anonymous

and the criteria to pick dates for duration calculation?

Re: Count Days Between Two Dates in Same Column

Anonymous — Thu, 09 Mar 2023 19:53:56 GMT

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

Re: Count Days Between Two Dates in Same Column

FreemanZ — Thu, 09 Mar 2023 20:04:26 GMT

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)

Re: Count Days Between Two Dates in Same Column

Anonymous — Thu, 09 Mar 2023 20:05:55 GMT

Thank you! I will try this!