Forum Discussion
Helper IIIFind a value / LOOKUPVALUE
Good morning all,
Not finding a solution to my problem, I try to work around the thing.
I want to display on the same line the start date (task.taken) and the end date (task.completed) of the same "task_id".
I illustrated this.
I carried out tests with in particular the LOOKUPVALUE formula that you can find in .testrecherche and .testrecherche2.
the difficulty I encounter is that the value "task-id" or the one found in "task_id_debut / fin" can be repeated. I need to have the one that comes right after (chronological order of created_at).
Thanks for your help.
Mathieu
PBIX: https://www.dropbox.com/sh/61ae2dtbmobl8tp/AAATpy0sRBMGRziU0Xe7Sy_Za?dl=0
7 Replies
- Greg_Deckler
Community Champion
MathieuF - So what is the expected output then?
MathieuFHello Greg_Deckler and thank you for looking into my case.
I want to calculate the time between the start of the action and the end of the action.
Here is the result that I hope (before proceeding with the subtraction).Mathieu
- Greg_Deckler
MathieuF See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousYou may need to use MINX in your example.
- MathieuF
I had seen your article and wanted to adapt it without success.
I tested your formula, but it didn't give the same result (I removed the subtraction to test.
.testRecherche3 =
VAR __Current = journal_forum[created_at]
VAR __PreviousDate = MINX(FILTER(journal_forum;journal_forum[created_at] < EARLIER(journal_forum[created_at]));journal_forum[created_at])VAR __Previous = MINX(FILTER(journal_forum;journal_forum[created_at]=__PreviousDate);journal_forum[created_at])
RETURN
//__Current -
- __PreviousI tried this formula, but it only works for the first part. Not the second.
.testRecherche2 = IF(ISBLANK(journal_forum[Task_id_debut])=BLANK();CALCULATE(MIN(journal_forum[created_at]);FILTER(ALL(journal_forum);journal_forum[Task_id_fin]=EARLIER(journal_forum[Task_id_debut])));BLANK())Mathieu
- Greg_Deckler
MathieuF -
I tried this formula, but it only works for the first part. Not the second.
.testRecherche2 = IF(ISBLANK(journal_forum[Task_id_debut])=BLANK();CALCULATE(MIN(journal_forum[created_at]);FILTER(ALL(journal_forum);journal_forum[Task_id_fin]=EARLIER(journal_forum[Task_id_debut])));BLANK())You would need to include an additional filter like:
IF(ISBLANK(journal_forum[Task_id_debut])=BLANK();CALCULATE(MIN(journal_forum[created_at]);FILTER(ALL(journal_forum);journal_forum[Task_id_fin]=EARLIER(journal_forum[Task_id_debut]) && journal_forum[created_at]>EARLIER(journal_forum[created_at])));BLANK())
- MathieuF
Hello,
I'm sorry, my problem is still there.
How to make it take into account the next non empty line?
Should I use the ALLNOBLANK function? And how ?
Thanks in advance.
