cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sjehanzeb
Resolver I
Resolver I

Net Positive Values at a Particular time.

I have table of individual charges and payment for a customer. eg. 

 

Customer IDDateType Amount
11-Jan-2019Services A10,000
130-Mar-2019Payment-8,000
11-Jul-2019Services B6,000
110-Oct-2019Payment-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

 
1 ACCEPTED SOLUTION
sjehanzeb
Resolver I
Resolver I

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

 

View solution in original post

4 REPLIES 4
sjehanzeb
Resolver I
Resolver I

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
Resolver I
Resolver I

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
Resolver I
Resolver I

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:

v-kelly-msft_0-1614584846636.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.