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 everybody,
I did not find the issue, sorry il it exists.
I got a problem to find the delay between 2 dates with one id.
Ex :
id date
1 01/05/2017
2 02/05/2017
3 02/05/2017
2 04/05/2017
3 05/05/2017
I would like "null" and 2 days for n°2 and "null" and 3 days for n°3 (and null for n°1)
I would like to have the average too, so 1 days for n°2 and 1,5 for n°3 (and 0 for n°1)
Am i clear or not?
Thank you for all,
Ben
Solved! Go to Solution.
Hi @Anonymous,
I reproduce your scenario and get expected results.
Please create the calculated coulmn using the formula below step by step.
Rank = RANKX(FILTER(Test,Test[id]=EARLIER(Test[id])),Test[date],,ASC) Result = IF(ISBLANK(LOOKUPVALUE(Test[date],Test[id],Test[id],Test[Rank],Test[Rank]-1)),BLANK(),DATEDIFF(LOOKUPVALUE(Test[date],Test[id],Test[id],Test[Rank],Test[Rank]-1),Test[date],DAY)) Time = Test[Result]/Test[tx]
Best Regards,
Angelia
Something more,
See what I would like :
id date result
1 01/05/2017 null
2 02/05/2017 null
3 02/05/2017 null
2 04/05/2017 2
3 05/05/2017 3
...
@Anonymous
ben what are you trying to do, probably easier if you explain the business requirement, ie whats your objective
Proud to be a Super User!
Ok, I try to explain the purpose...
with "tx" a percent (0,25 -> a garbage/container 25% fulfilled, need to be dumped),
the date is the date we dump the garbage/container,
the id is the reference of the container.
id tx date result Time
1 0,25 01/05/2017 null /
2 0,5 02/05/2017 null /
3 0,25 02/05/2017 null /
2 1 04/05/2017 2 2
3 0,5 05/05/2017 3 6
...
The "Time" is the difference between the two last dates of the id divided by the tx.
It will be better with many "Time" to have one average time with each id.
Hi @Anonymous,
I reproduce your scenario and get expected results.
Please create the calculated coulmn using the formula below step by step.
Rank = RANKX(FILTER(Test,Test[id]=EARLIER(Test[id])),Test[date],,ASC) Result = IF(ISBLANK(LOOKUPVALUE(Test[date],Test[id],Test[id],Test[Rank],Test[Rank]-1)),BLANK(),DATEDIFF(LOOKUPVALUE(Test[date],Test[id],Test[id],Test[Rank],Test[Rank]-1),Test[date],DAY)) Time = Test[Result]/Test[tx]
Best Regards,
Angelia
OMG!!
Successfull!!!
Thanks a lot!!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |