Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |