Forum Discussion
COMPARE TOTAL VALUE IN DIFFERENT ROW
Could any body kindly help me on this please
I have data as table A, and I would like to make 'Revised qty' from Q'ty column with condition: If Q'ty of current Op.no is higher than Q'ty of previous Op.no, then Q'ty of previous Op. No = Q'ty of current Op.no.
- For example : Qty of Op. No2 is 1,002, it's higher than 598 of Op. No 1, then Revised qty of Op. No 1 will become 1002.
- I tried with EARLIER but due to my poor understanding, so only can calculate cummulative number 😂
Thanks in advance.
So you want to look at Date, Line, File No & Description as well, something like this?
yourNewColumn= VAR CurOp = yourTable[Op.No] VAR T1=FILTER(yourTable,yourTable[Description]=EARLIER(yourTable[Description])&&yourTable[Date]=EARLIER(yourTable[Date])&&yourTable[Line]=EARLIER(yourTable[Line])&&yourTable[File NO]=EARLIER(yourTable[File No])) VAR CurAcc = SUMX(FILTER(T1,yourTable[Op.No]=CurOp),[Qty]) VAR NextAcc = SUMX(FILTER(T1,yourTable[Op.No]=CurOp+1),[Qty]) RETURN IF(CurAcc<NextAcc,NextAcc,CurAcc)
6 Replies
So you need a DAX calculated column, looking at Op.No only, try it, modify the table name, column name accordingly
yourNewColumn = VAR CurOp = yourTable[Op.No] VAR CurAcc = SUMX(FILTER(yourTable,yourTable[Op.No]=CurOp),[Qty]) VAR NextAcc = SUMX(FILTER(yourTable,yourTable[Op.No]=CurOp+1),[Qty]) RETURN IF(CurAcc<NextAcc,NextAcc,CurAcc)Hi Vera_33 😊
I am appreciated for your advice, thanks for your time. It worked on my testing file with simple information.
- However, my original file has many information such as Date/Line/File No/Description, so the Dax is currenly is not being considered to those 04 types of information, it only consider by Op. No.
- I tried to learn Variable this morning ^^ but my deadline is almost over, so I do not have time to deep look into it. Also, I tried with selected date (first of condition), but still not workable.
Can you please advise me how to insert more conditions to the Dax such as Date, Line, File No & Description ?
So you want to look at Date, Line, File No & Description as well, something like this?
yourNewColumn= VAR CurOp = yourTable[Op.No] VAR T1=FILTER(yourTable,yourTable[Description]=EARLIER(yourTable[Description])&&yourTable[Date]=EARLIER(yourTable[Date])&&yourTable[Line]=EARLIER(yourTable[Line])&&yourTable[File NO]=EARLIER(yourTable[File No])) VAR CurAcc = SUMX(FILTER(T1,yourTable[Op.No]=CurOp),[Qty]) VAR NextAcc = SUMX(FILTER(T1,yourTable[Op.No]=CurOp+1),[Qty]) RETURN IF(CurAcc<NextAcc,NextAcc,CurAcc)
Hi Vera_33 😊
I am appreciated for your advice, thanks for your time. It worked on my testing file with simple information.
- However, my original file has many information such as Date/Line/File No/Description, so the Dax is currenly is not being considered to those 04 types of information, it only consider by Op. No.
- I tried to learn Variable this morning ^^ but my deadline is almost over, so I do not have time to deep look into it. Also, I tried with selected date (first of condition), but still not workable.
Can you please advise me how to insert more conditions to the Dax such as Date, Line, File No & Description ?
