Forum Discussion
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 logical function:
if (arrival date [3/15/2021] <> blank () && end date [3/17/2021] <> blank () && date I finish [3/9/2021] <> blank (), "ok", "verify") but in this last part I don't know what to use to see the previous cell,
I would be grateful if you can help me,
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 ))RETURNIF (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
14 Replies
AGonza123 , You can add an index column and get previous row
last END DATE = maxx(filter(Table,[index] = earlier([index])-1),[END DATE])
you can compare his with end date
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bisame you can do for another date
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 ))RETURNIF (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
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
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.
-
