Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculating transaction value based on balance

Hi,

I have a dataset with cust_ID, account_bal, date, time,transaction_id fields. Each time a transaction happens, the account balance gets updated(could increase/decrease). There are multiple transactions in a day. Would appreciate if someone could assist with calculating transaction value for each customer transaction. 

 

Thanks   

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new column like

     

    new column =
    var _id = maxx(filter(table, [cust_id] =earlier([cust_id]) && [transaction_id] < earlier([transaction_id])),[transaction_id])
    return
    [balance] -maxx(filter(table, [cust_id] =earlier([cust_id]) && [transaction_id] =_id),[balance])

     

    In place of transaction_id, you can use combined date-time field

     

    datetime = [Date]+[time]

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The calculated column still doesn't provide with desired results. Table shows filtered data for 1 customer. Need to create a measure as dataset is huge. (Assuming date-time as single merged column)

     

    Thanks  

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ,

      What's your expected result? Could you please provide your expected result with specific examples and calculation logic? Thank you. For example, the data in your screenshot, what you want is to get the summarize balance(463.96+424.01+401.05+391.94+....) for per customer? 

      Best Regards