Running Total Balance based on pulling history

[avi123]

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)

[Ashish_Mathur]

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.

[v-nuoc-msft]

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.

[bhanu_gautam]

@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]

@bhanu_gautam 
Hi

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