COMPARE TOTAL VALUE IN DIFFERENT ROW

[thongnguyen2414]

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.

[Vera_33]

Hi @thongnguyen2414 

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)

[Syndicate_Admin]

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 ? 

[thongnguyen2414]

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 ? 

[Vera_33 · Accepted solution]

Hi @thongnguyen2414 

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)

[thongnguyen2414]

Dear Vera_33

- It worked. Your Dax is awsome to me, I have learnt many thing ^^

- After I submitting that result, my manager has changed his mind again with new calculation requirement, but I will try my best 🙂

Thank you for your help 

[Vera_33]

Hi @thongnguyen2414 

My pleasure. I am not a fan of DAX calculated column...normally go with M or DAX measures. It is a very interesting learning journey, enjoy!

Vera