Forum Discussion

AGonza123's avatar
AGonza123
Frequent Visitor
5 years ago
Solved

Formula to compare previous cells

ARRIVAL DATE    END DATE 2/3/2021                 9/3/2021 15/3/2021             17/3/2021   Hello! I would have a doubt which formula to use to compare previous cells, I want to perform a logica...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, AGonza123 

    Please check the below picture and the measure below, which are for creating a measure.

    The link to the sample pbix file is down below.

     

     

    https://www.dropbox.com/s/6s2vcied9g2blg4/agonza.pbix?dl=0 

     

    Result =
    VAR currentenddate =
    MAX ( 'Table'[End Date] )
    VAR enddatepreviousrow =
    CALCULATE (
    LASTNONBLANK ( 'Table'[End Date], MAX ( 'Table'[End Date] ) ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[End Date] < currentenddate )
    )
    RETURN
    IF (
    ISFILTERED ( 'Table' ),
    IF (
    SELECTEDVALUE ( 'Table'[Arrival Date] ) <> BLANK ()
    && SELECTEDVALUE ( 'Table'[End Date] ) <> BLANK ()
    && enddatepreviousrow <> BLANK (),
    "ok",
    "verify"
    )
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, AGonza123 

Please check the below picture and the measure below, which are for creating a measure.

The link to the sample pbix file is down below.

 

 

https://www.dropbox.com/s/6s2vcied9g2blg4/agonza.pbix?dl=0 

 

Result =
VAR currentenddate =
MAX ( 'Table'[End Date] )
VAR enddatepreviousrow =
CALCULATE (
LASTNONBLANK ( 'Table'[End Date], MAX ( 'Table'[End Date] ) ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[End Date] < currentenddate )
)
RETURN
IF (
ISFILTERED ( 'Table' ),
IF (
SELECTEDVALUE ( 'Table'[Arrival Date] ) <> BLANK ()
&& SELECTEDVALUE ( 'Table'[End Date] ) <> BLANK ()
&& enddatepreviousrow <> BLANK (),
"ok",
"verify"
)
)
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

  • AGonza123's avatar
    AGonza123
    Frequent Visitor
    5 years ago

    Hello @Jihwan_Kim excuse me, a matter of minutes ago, I had made a query on how to compare previous cells, with the measure that you shared, it turned out great but now I have a problem, I wanted to attach more dates and in this case put additional conditions: if (arrival date [3/15/2021] <> blank () && end date [3/9/2021] <> blank (), "verify", if(arrival date [3/15/2021] <> blank () && end date [3/17/2021] <> blank () && date I finish [3/9/2021] <> blank (), "verificar", "ok") but I think that the measure no longer Register it, I would be very grateful if you could help me one last time

     

     

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, AGonza123 

      Thank you for your message.

      Sorry that I quite do not understand your last question.

      I don't understand your last measure and I think it is saying a different thing than what you asked before.

      Please write like below for your new table.

      Or, please let me know how your desired outcome looks like.

       

       

       

      -

      • AGonza123's avatar
        AGonza123
        Frequent Visitor
        5 years ago

        Hello, thank you very much for your answer, I tried to explain myself better, at the beginning I thought that just showing that example of dates would work but as I have more data it gets complicated, if in power bi with that example of two dates I could already join the columns of my table a with columns of table b creating a new table but I don't know if it is the right thing to do, in the attached link you can find part of the tables and the result of how I hope it can be done, each store has its sku with dates of arrival and finish and what I want to achieve is that by having more than 10 stores united, the formula can perform this logical operation for each point, that is, it only analyzes the relationship of dates corresponding to that point and then the next point analyzes their corresponding dates I hope you understand me

        https://drive.google.com/file/d/1cmjEBt4E9Yw97PwLg-Id681B-6qdYvKh/view?usp=sharing 

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, AGonza123 

      I just checked your message. Thank you for your message.

      Sorry to say that I am now a bit more confused. I think my measure does not work if you are working with two tables.

      I am still not sure whether your screenshot in the message is coming from two different tables or from one table. In order to prevent misunderstanding, please share your sample file's link here, then I can try to come up with a more accurate measure.

      Thank you.