Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vitoraraujo
Frequent Visitor

How to cumulative sum with date and player filters ?

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.

IAPs.jpg

 

 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?

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

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

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.