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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
vitoraraujo
Frequent Visitor

Monthly KPI's

 

Hi there.

 

I want to create a monthly report that shows me some KPI's, from the data pulled from our database about the players.

 

IAPs.jpgreport.jpg

I have this Table called IAPs (In App Purchases)

 

In this table I have all the players that bought, in the "Player" Collumn.

 

The "Player date created" column shows when the player entered the game.

 

"DATECREATED" Shows when was the purchase made.

 

"Id" Shows the purchase Id, wich is the unique field in this table, all the other ones repeat.

 

Month cohort is the datediff between "Player date created" and "DATECREATED".

 

I want to make a cohort analysis that watches the players that keep buying trhough months. Thats what the selected table means in the second picture. The formula is as follows:

Conversion rate = divide([buyers per month], [New Players Month])

buyers per month = 
 CALCULATE(DISTINCTCOUNT([Player]),ALLEXCEPT(IAPs,IAPs[player date created (bins)]),IAPs[month cohort] >= EARLIER(IAPs[month cohort]))

New Players Month =  Calculate(countrows('Player Master'), ALLEXCEPT('Player Master','Player Master'[DateCreated (bins) 2]))

New Players month is all correct, I double checked. The problem is in buyers per month.

The thing is Buyers per month distinct counts players buying in months cohort that already passed when I refresh the report!

 

(player created: November; cohort : 1; purchase month: december) This player can never buy again once december 2016 has passed, but when I resfresh the report, the conversion rate for this conditions change!

 

Either I invented the time machine or I'm missing something. 

 

Can someone help?

1 ACCEPTED SOLUTION

Hi @v-qiuyu-msft

I managed to solve this issue, your inclination was ritgh, the final formula that sucessfully calculates the Cohort KPI is the following:

buyers per month = CALCULATE(DISTINCTCOUNT(IAPs[player]),ALLEXCEPT(IAPs,IAPs[player date created mensal], IAPs[month cohort]), IAPs[DATECREATED (bins)]>= EARLIER(IAPs[DATECREATED (bins)]))

Then I just divided buyers per month by the new players that entered that month and obtained the correct answer : 


correct kpis.jpg

 

 I used to think that the data should decrease as the month cohort raised, that thought is correct although that's not the proper way to calculate it. The values should decrease as the DATECREATED (the date of the events; i.e. the actual Time, with capital T) passes.

View solution in original post

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @vitoraraujo,

 

Can you clarify the formula to count buyers each month? I notice you use IAPs[month cohort] >= EARLIER(IAPs[month cohort])), why don't use the [Player date created] or [DATECREATED]?

 

Please share some sample data and desired results for our analysis if possible.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft

I managed to solve this issue, your inclination was ritgh, the final formula that sucessfully calculates the Cohort KPI is the following:

buyers per month = CALCULATE(DISTINCTCOUNT(IAPs[player]),ALLEXCEPT(IAPs,IAPs[player date created mensal], IAPs[month cohort]), IAPs[DATECREATED (bins)]>= EARLIER(IAPs[DATECREATED (bins)]))

Then I just divided buyers per month by the new players that entered that month and obtained the correct answer : 


correct kpis.jpg

 

 I used to think that the data should decrease as the month cohort raised, that thought is correct although that's not the proper way to calculate it. The values should decrease as the DATECREATED (the date of the events; i.e. the actual Time, with capital T) passes.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.