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

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's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!