Net Positive Values at a Particular time.

[sjehanzeb]

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	Services B	6,000
1	10-Oct-2019	Payment	-9000

I need to develop receivable of at specific intervals in time (MMM-YY). Simply plotting the Amount column in values establishes that purpose, though when the balance goes negative as in the case of 10th October (Balance = -1,000). the amount also goes negative. Technically it should not go below zero. Is there any way to achieve this? 

One way is a add a calculated column to the table that adds the values above - is that the way to go or is there a better way

[sjehanzeb · Accepted solution]

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]

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). 

[sjehanzeb]

the calculated column (balance) is not working either, if I sum the figure from previous values, they are inflated as balance is already a sum. If I don't sum it,

• it is adding multiple balance of same customer in a month
• not adding any value of customer if it does not have a transaction in that month

if we go with a balance approach, it should show a monthly single line item by each customer every month (based on the last transaction) regardless of any transaction in that month. 

[v-kelly-msft]

Hi  @sjehanzeb ,

Create a measure as below:

Measure = 
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Customer ID]=MAX('Table'[Customer ID])))

 And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!