Forum Discussion

avi123's avatar
avi123
New Member
1 year ago

Running Total Balance based on pulling history

Hi,

i am new here do if something is not clear i will add 🙂

in my company there is a table of deals and table of orders - from each deal let say 100000 the customer can pull from his deal the products he want till he finished the 100000
on each time, the ERP server of the system update the deal balance as given value and not giving me the balance for each date.

i tried to calculate the running balance by myself based on the orders date(UDATE) and the orders amount(Qprice)

my measure as below:

RunningTotalBalance =
MAXX(
    RELATEDTABLE(DEAL_Alpha), DEAL_Alpha[QPRICE]  
) -
CALCULATE(
    SUM(ORDERS_Alpha[QPRICE]),  
    FILTER(
        ALL(ORDERS_Alpha),  
        ORDERS_Alpha[DEAL] = MAX(DEAL_Alpha[DEAL]) &&  
        ORDERS_Alpha[UDATE] <= MAX(ORDERS_Alpha[UDATE])
         
    )
)
 
i tried to do matrix table with this measure but for some reason for customers that has more than 1 deal it takes the last calculated balance for the last order (0) in the first line of totals instead of taking the sum of 19560 + 17360(197000-160080)

 

Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    avi123 , Try using below measure

     

    RunningTotalBalance =
    VAR CurrentDeal = MAX(DEAL_Alpha[DEAL])
    VAR CurrentDate = MAX(ORDERS_Alpha[UDATE])
    VAR DealInitialBalance = MAXX(FILTER(DEAL_Alpha, DEAL_Alpha[DEAL] = CurrentDeal), DEAL_Alpha[QPRICE])
    VAR OrdersTotal = CALCULATE(
    SUM(ORDERS_Alpha[QPRICE]),
    FILTER(
    ALL(ORDERS_Alpha),
    ORDERS_Alpha[DEAL] = CurrentDeal &&
    ORDERS_Alpha[UDATE] <= CurrentDate
    )
    )
    RETURN
    DealInitialBalance - OrdersTotal

    • avi123's avatar
      avi123
      New Member
      1 year ago

      bhanu_gautam 
      Hi

       

      tx for the help but it giving me the same result and still 0 in the total per the cust_ID

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi avi123 

     

    Thank you very much bhanu_gautam for your prompt reply. Allow me to share something.

     

    You can try adjusting the measure to make sure it handles each customer and transaction correctly:

     

    RunningTotalBalance =
VAR CurrentDeal = MAX(DEAL_Alpha[DEAL])
VAR CurrentDate = MAX(ORDERS_Alpha[UDATE])
VAR DealAmount = MAXX(FILTER(ALL(DEAL_Alpha), DEAL_Alpha[DEAL] = CurrentDeal), DEAL_Alpha[QPRICE])
VAR OrdersToDate = 
    CALCULATE(
        SUM(ORDERS_Alpha[QPRICE]),
        FILTER(
            ALL(ORDERS_Alpha),
            ORDERS_Alpha[DEAL] = CurrentDeal &&
            ORDERS_Alpha[UDATE] <= CurrentDate
        )
    )
RETURN
    DealAmount - OrdersToDate

     

    If you're still having problems, please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.