Sum up sales from customers

[Anonymous]

Hi everyone,

I have a problem where i am trying to sum up the sales (create a balance) for my customers based on when the sale was made.

I have the posting date of the sale, along with the customer and the amount.

I would like to sum up the sales based on the date. 

In my example i have the wanted column "balance", which sums up the amount from the posting date and earlier posts.

Without taking the "entry no." into account (since that complicates things). I wouldn't mind having the balance for the date being the total of all sales as seen for customer B's two posts on the 30/01/2019. The sales are both 5, but since i just look at the date, the total would say 10.

What would be even nicer is the column "better balance" where it simply looks at the "entry no." and "customer" to sum up the row and previous rows fulfilling the criteria. So the first "balance" for B would be 5, and then 10 as it finds the next entry from customer B.

I just can't seem to get it to work. i have tried EARLIER, FILTER, SELECTEDVALUE, FINDVALUE and others.

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following calculated column formulas if they suitable for your requirement:

balance =
CALCULATE (
    SUM ( Table[Sale] ),
    FILTER (
        ALL ( Table ),
        [Customer] = EARLIER ( Table[Customer] )
            && [Posting Date] = EARLIER ( Table[Posting Date] )
    )
)


Better balance =
CALCULATE (
    SUM ( Table[Sale] ),
    FILTER (
        ALL ( Table ),
        [Customer] = EARLIER ( Table[Customer] )
            && [Posting Date] <= EARLIER ( Table[Posting Date] )
    )
)

Regards,

Xiaoxin Sheng

[Anonymous]

The code you pasted for "balance" and "better balance" is the same. Was that an accident?

[v-shex-msft]

Hi @Anonymous ,

Nope, they are different. One of the formula condition is 'equal to' current post date, the other one is 'less than or equal to' current post date.

So the second one will look up the cumulative result based on current date.

Regards,

Xiaoxin Sheng