Forum Discussion
Count Days Between Two Dates in Same Column
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
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])RETURNDATEDIFF(_datepre, _date, DAY)
6 Replies
hi Anonymous
doable. would need the info of other columns to locate date rows. could you enrich your data sample?
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])RETURNDATEDIFF(_datepre, _date, DAY)