How do write a calculated field in tableau to pick the latest record based on column criteria?

Question

• Date RefNum Value TxnType
• Mar-23 SS 20 BillDoc
• Apr-23 G4 40 BillDoc
• May-23 HT 85 BillDoc
• Jan-23 R3 3 Credit
• Feb-23 R3 5 Credit
• Aug-23 D7 10 Credit
• Sep-23 D7 15 Credit

write the calculated field formula that gives you total value by summing the value for the billdoc TxnType but for Credit TxnType it picks the value that corresponds with the most recent date record for each RefNum. So the total value should be 20+40+85+5+15

Answer 1

You can write an LOD calc to find the date of the last credit transaction for each RefNum, call it say, Last_Trans_Date_Per_RefNum defined as

{FIXED [RefNum] : MAX(IF [TxnType] = "Credit" THEN [Date] END) }

Read about Level of Detail calculations in the Tableau help to see how this works.

Then define another field, call it say, New Value defined as

If [Txn Type] = "BillDoc" OR [Date] = [Last_Trans_Date_Per_RefNum] THEN [Value] END

Note this approach ignores the question of what to do if there are multiple credit transactions on the last day. As written, it would include every credit transaction on the last day for a RefNum