cancel
Showing results for
Did you mean:
Highlighted
Trevor_ Frequent Visitor

## How to Add a Column with the Number of Days Between Two Rows

Hi, all. I have a table with the following:

```Date       | Score| Leader
2018-01-14 | 1234 | Test1
2018-01-22 | 1456 | Test1
2018-01-23 | 2345 | Test1
2018-01-26 | 1234 | Test2```

What I'd like to do is add a column that includes the number of dates between the row and the row coming after it from a date perspective. So something like:

```Date       | Score| Leader  | DayBetween
2018-01-14 | 1234 | Test1   | 8
2018-01-22 | 1456 | Test1   | 1
2018-01-23 | 2345 | Test1   | 3
2018-01-26 | 1234 | Test2   | 3```

Any ideas on how to add a column that inludes the number of days from [Date] to the [Date] field in the next row? Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions Super User

## Re: How to Add a Column with the Number of Days Between Two Rows

Hi,

Try this calculated column formula

=CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))-Data[Date]

Hope this helps. Super User

## Re: How to Add a Column with the Number of Days Between Two Rows

You are welcome.  See if this works

=IF(CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))=0,BLANK(),CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))-Data[Date])

8 REPLIES 8 Super User

## Re: How to Add a Column with the Number of Days Between Two Rows

Have a look at my video here https://youtu.be/xN2IRXQ2CvI

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI. Super User

## Re: How to Add a Column with the Number of Days Between Two Rows

Hi,

Try this calculated column formula

=CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))-Data[Date]

Hope this helps.

Trevor_ Frequent Visitor

## Re: How to Add a Column with the Number of Days Between Two Rows

I'm trying this:

`DaysBetween = CALCULATE(MIN(Leaders[Date]),FILTER(Leaders,Leaders[Date]>EARLIER(Leaders[Date]))-Leaders[Date])`

But get the following error:

`A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.` Super User

## Re: How to Add a Column with the Number of Days Between Two Rows

Hi,

There was a missing bracket in my previous post.  I have now corrected for that.  Also, mine is a calculated column formula (not a measure).

Trevor_ Frequent Visitor

## Re: How to Add a Column with the Number of Days Between Two Rows

Great; thanks!

Only one last item--the most recent day (today) shows up with a score of -43498. Any adjustment I can make so that doesn't happen? Thanks! Super User

## Re: How to Add a Column with the Number of Days Between Two Rows

You are welcome.  See if this works

=IF(CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))=0,BLANK(),CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))-Data[Date])

Trevor_ Frequent Visitor

## Re: How to Add a Column with the Number of Days Between Two Rows

Perfect! Super User

Thank you.