Forum Discussion

MathieuF's avatar
MathieuF
Icon for Helper III rankHelper III
5 years ago

Find a value / LOOKUPVALUE

Good morning all, Not finding a solution to my problem, I try to work around the thing. https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculation-between-2-dates-based-on-the-same-value/m-...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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 - __Previous

 

You may need to use MINX in your example.

  • MathieuF's avatar
    MathieuF
    Icon for Helper III rankHelper III
    5 years ago

    Greg_Deckler

    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 -
    - __Previous

     

     

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

     

     

    Mathieu

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        MathieuF
        Icon for Helper III rankHelper III
        5 years ago

        Helolo Greg_Deckler 

        Thank you very much, thanks to you, I am reaching my goal.

        There is one small problem.
        Indeed, if I take the example of user_id 5be2f44514bf8a39cb988ff5 and task_id 5fcdf9e9bd0e542b263be8a8 (same with 5fcbc85e71336801d1debc25) we can see that there are interactions between (content.replied) which give empty fields. So I think EARLIER does not detect the next value.

         


        How to cure it ?

        My files: https://www.dropbox.com/sh/61ae2dtbmobl8tp/AAATpy0sRBMGRziU0Xe7Sy_Za?dl=0


        Columns concerned:
        task_id_debut
        task_id_fin
        .testrecherche2
        .created_at_debut
        .Measure1 / .testrecherche4

         

        Moreover, there are surely simpler, without using so many columns.


        Thank you for your precious help.

         

        Mathieu