Formula to compare previous cells

[AGonza123]

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,

[Jihwan_Kim · Accepted solution]

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]

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]

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.

[Jihwan_Kim]

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]

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]

Hi, @AGonza123 

Thank you for your message.

Terribly sorry to say that I don't know which part I am missing.

Perhaps I did not check your previous message or comment.

Sorry but please clarify the below.

- The initial equation was, if three cells are not blank, then "OK". But do you want to change it to If three cells are not blank, then "Verify"? Am I correct?

-what is store arrival? -> in which table can I find this column?

-what is store resultable? -> in which table can I find this column?

-what is sku arrival? -> in which table can I find this column?

[AGonza123]

I had not seen your answer, excuse me because I did not express myself correctly, in the file indicate the terms as I mentioned them and with the modified formula indicating the cells, as I had previously commented I have two tables (arrival table and end table) and what I did was join them in power bi to what would be the "resultable" side table (I thought the most convenient) and on that table I would be needing to apply the formula, I hope I have expressed myself better this time, thank you very much!

https://www.dropbox.com/scl/fi/q4qpyyo6s6uiknleiycjl/STORE-1P.xlsx?dl=0&rlkey=qbalmqs2dl99nh4q8y3sgr...

[Jihwan_Kim]

Hi, @AGonza123 

Thanks for sharing.

Please check the below and the link.

Assumption:

- Sorry that I cannot know how to define the week number. I assume you have your own financial week number, so I matched to your sample.

- Sorry that I cannot differentiate Laureles week10 11th march 12 Q arrival, and Laureles week10 11th march 36 Q arrival. So I aggregated and total Q 48.

- I tried not to create additional table (Result Table). I could create the visualization like below.

- I assumed that you changed the condition of generating "Verify" and "Ok". Please check this, because I am really confusing about this. This makes the whole question different than your very first question and I think this is not the same question as your first question. You may say what is the difference between changing the result from A to B. Then, please ask to get the result A or B, instead of writing a word that has a certain meaning. The opposite meaning of the result drives me to think whether I wrote the DAX measure totally wrong way or not.

- I failed to understand where is the situation that Arrival Date is Blank and at the same time end date is not blank (ingreso sin pedido). I assume you did not share all your data.

 

Q Arrival =

SUM(Arrival[Q])

Q End =

SUM('End'[Q])

Result =

IF( ISFILTERED(Dates[WEEK]),

SWITCH( TRUE(),

[Q Arrival]<> BLANK() && [Q End] <> BLANK(), "Verify",

[Q Arrival] = BLANK() && [Q End] = BLANK(), "SQ",

[Q Arrival] = BLANK() && [Q End] <> BLANK(), "ingreso sin pedido",

"OK"))

https://www.dropbox.com/s/530m71xw4a7ifzw/agonzav2.pbix?dl=0 

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]

Hello, sorry for answering recently, I tried to apply the suggested measure but I don't think I have found a solution, and answering your questions, I changed the conditions because my first example was very simple and having more data changed the situation and regarding the date Previous blank as I had consulted now if I already have those cases, in the following link I leave the pbi of what I did with the first measure that I had indicated (it should be noted that I added two series of conditions to the measure that had been suggested to me ) but I think I did not do it correctly because I did not get the expected results, it is also shown in the file that of tables a and b, I joined in another table and put a measurement that indicates the weeks. Sorry if the fields have names other than arrival and end but these would be the original data, also the result that I hope remains the same!
I hope not to confuse you more again

Terms
1) three different cells of white,
2) a cell equal to blank and any of the other two equal to blank,
3) a cell equal to white and any of the other two different from white

https://www.dropbox.com/sh/ikok13fofy3nyn0/AABGUF2PvBjYDt2iYbhX8m8ia?dl=0

[Jihwan_Kim]

Hi, @AGonza123 

I am not sure whether the below is what you are looking for.

Please kindly check.

https://www.dropbox.com/s/nu1qvf4nh5sf5hv/PRUEBA%20QUIEBRES.pbix?dl=0 

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]

Thank you very much for answering! I saw the file but what I would be needing is to compare not the quantities, but the dates of the column "quiebres" vs the column "compras" of the same row and also look at the previous cell, because what I would like is for me throw a kind of alert when there are subsequent dates! where the conditions would be the same as if my break cell is different from white and the other one for "compras" is different from white and the previous "compras" date is also different from white, "verify", if the break date cell is the same to blank, and the purchase date cell as the previous purchase date is equal to blank, "SQ", and if the break date is equal to blank and any of the other two "compras" cells are other than blank, " entry without request ", I took as an example the measure that had been suggested to me the first time and added the conditions but it did not bring me the correct results, it is found in the following link, is it possible to use that measure?

In the image you can see the dates of the "quiebre" column and the "compras" column and I want to apply the condition for each row of the "quiebre" and "compras" dates, and the previous "compras" date.

https://www.dropbox.com/sh/ikok13fofy3nyn0/AABGUF2PvBjYDt2iYbhX8m8ia?dl=0

[Jihwan_Kim]

Hi, @AGonza123 

Thank you for your message.

I cannot access your shared file. It asked to enter the password.

Please kindly check.

[AGonza123]

Excuse me, and now could you confirm if I let you enter, both links are the same

https://www.dropbox.com/scl/fi/uqwr4eqk0exsa6pub63p7/STORE-1P.xlsx?dl=0&rlkey=6xvlxa97zmc2tgy22pdmu9... 

[amitchandak]

@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