Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula

 

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

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

 

Is there a way to make it work with Direct Query?

(To be specific, FILTER and CALCULATE don't seem to work in Calculated Columns in Direct Query mode.)

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.

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).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

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])


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Perfect!

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.