cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Trevor_ Frequent Visitor
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
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
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
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
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
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
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
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
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
Frequent Visitor

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

Perfect!

Super User
Super User

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

Thank you.