Forum Discussion
Formula to compare previous cells
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
Super UserHi, 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?
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!
- 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"))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, 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 againTerms
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 whitehttps://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