Forum Discussion

sjehanzeb's avatar
sjehanzeb
Resolver I
5 years ago
Solved

Net Positive Values at a Particular time.

I have table of individual charges and payment for a customer. eg.    Customer ID Date Type  Amount 1 1-Jan-2019 Services A 10,000 1 30-Mar-2019 Payment -8,000 1 1-Jul-2019 S...
Need Help
Tips and Tricks
  • sjehanzeb's avatar
    sjehanzeb
    5 years ago

    So, I resolved it.
    1. using calender table summarized every MMM-YY in the range
    2. using the transaction table, summarized all IDs 
    3. create a cross table with all values from both tables.
    4. Then using addcolumn (to 3) created a calculated column which shows sum of previous activity of individual id. 

     

    var mytable1 = ADDCOLUMNS(
    CROSSJOIN(
        SUMMARIZE(Calender, Calender[Year Month], Calender[Year Month Number]), 
        SUMMARIZE(SF, SF[ID])), 
        "Amount",  CALCULATE(sum(SF[Amount]), FILTER(SF, RELATED(Calender[Year Month Number]) <= EARLIER(Calender[Year Month Number]) && SF[Customer ID]=EARLIER(SF[Customer ID]))

     

sjehanzeb's avatar
sjehanzeb
Resolver I
5 years ago

What I am looking for is that the amount should not go negative for individual customer. 

 

1. It should be a running total of individual customer

2. Individual customer cannot go negative

 

As discussed, I created a calculated column with each transactions and forced it to not go negative. The problem with that solutions is 

1. When I ran (running total) sum of the calculated column it added multiple balances of the same customer in the given period

2. When I did not (running total) sum the column, the balances of the month where the user did not have any transaction did not show at all. 

 and where the customer had multiple transactions in a period it totaled it also. 


Ultimately, I beleive this could be resolved through creating a seperate table based on this data that shows monthly balance of individual customer whether they had the transactions or not. (I have been trying but have not succeeded in this atempt).