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

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