Forum Discussion
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 | 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
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]))
4 Replies
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]))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.
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,
KellyDid I answer your question? Mark my post as a solution!
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).
