Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi,
Try this calculated column formula
=CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))-Data[Date]
Hope this helps.
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])
Hi,
Try this calculated column formula
=CALCULATE(MIN(Data[Date]),FILTER(Data,Data[Date]>EARLIER(Data[Date])))-Data[Date]
Hope this helps.
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).
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])
Perfect!
Thank you.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |