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