How to cumulative sum with date and player filters ?

[vitoraraujo]

Hi I make games and I have a problem.

Specially with a calculated column (it can be a measure too, if someone could make it work).

The objective is to have a column in which I could cumulatively sum the money spent by each player. Every time he buys something the value should be incremented by the value of the product he bought, and the cumulative sum should stop and reset when the events of the first player end.

I have three main tables, first, one with unique transactions, the second with unique events, and the third with unique players.

table with the transactions value, by day.

 The column should be in this transactions table, making the sum of each purchase made by each player through time, and should stop and reset when we change player.

afterwards I want to create a measure of Average Revenue per User by Days Cohort (the numbered days since the player joined the game).

Can someone help figure this out?

[v-huizhn-msft · Accepted solution]

Hi @vitoraraujo,

>>The objective is to have a column in which I could cumulatively sum the money spent by each player.

You’d better create a calculated column which calculates cumulatively sum the money of each player using the following formula. Based on my understanding, the DATECREATED is the date of player spent.

=CALCULATE(SUM(transaction_table[Value]),FILTER(ALLEXCEPT(transaction_table, transaction_table [Player]), transaction_table [DATECREATED]<=EARLIER(transaction_table [DATECREATED])))

>> The column should be in this transactions table, making the sum of each purchase made by each player through time, and should stop and reset when we change player.

How to determine one player is stopped and changed? Form the given table in screenshot, which column is used to decide the player is stopped? Please share more details for further analysis.

>> afterwards I want to create a measure of Average Revenue per User by Days Cohort (the numbered days since the player joined the game.

In this case, I still use DATECREATED to calculate the numbered days. If this is incorrect, please post the sample data. First you should create the difference between max and min DATECREATED, then create the total money, and divide the days finally.

Differ:=DATEDIFF(CALCULATE(MIN(Test[DATECREATED]),ALLEXCEPT(Test,Test[Player])),CALCULATE(MAX(Test[DATECREATED]),ALLEXCEPT(Test,Test[Player])),DAY)
Total:=CALCULATE(SUM(Test[Value]),ALLEXCEPT(Test, Test[Player]))
Average:=Test[Total]/Test[Differ]

Or you can create one measure using the formula below.

Average:= CALCULATE(SUM(Test[Value]),ALLEXCEPT(Test, Test[Player]))/ DATEDIFF(CALCULATE(MIN(Test[DATECREATED]),ALLEXCEPT(Test,Test[Player])),CALCULATE(MAX(Test[DATECREATED]),ALLEXCEPT(Test,Test[Player])),DAY)

Best Regards,
Angelia

[vitoraraujo]

Hi @v-huizhn-msft, that solved the column part of the problem, and we figured out how to create the correct measure, which envolved ome averages and only works on specific graphs, but thanks to your method we were able to build it. Thank you.