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
gaboquin
Frequent Visitor

New meassure count users created in X date + X days

Hi, 

 

I have two tables: 

 

games: 

game_created: MM/DD/YYYY 

 

users: 

user_created: MM/DD/YYYY 

 

I would like to create two meassures:

 

1. Old Users = COUNT users that where created 7 days before game_created, or: count(user_id) where user_created + 7 days > game_created. 

 

2. New Users = COUNT users that where created 6 days or less than game_created, or: count(user_id) where user_created + 6 days < game.created. 

 

Any help will be appreciated! 
Thanks!

1 ACCEPTED SOLUTION

Actually I solved it this way: 

Jugadores =
SELECTCOLUMNS(
FILTER(NATURALINNERJOIN(users,game_entries),[private]=0&&[entry_currency]="real"),
"user id",
users[id],
"date created",
INT(FORMAT(users[created],"YYYYmm")),
"date game",
FORMAT(game_entries[_game_date],"YYYY-mm"),
"difference",
INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")),
"new",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) =0,game_entries[user_id],BLANK()),
"old",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) >0,game_entries[user_id],BLANK()),
"game id",
game_entries[game_id]
)


View solution in original post

8 REPLIES 8
mauriciosotero
Resolver III
Resolver III

Try this:

 

Create a collumn in the Game Table with the sintaxe:

 

USER_AGE = IF(DATEDIFF(LOOKUPVALUE(USER[CREATED];USER[ID];Game[USER_ID]);Game[DATE_CREATED];DAY)<7;"New User";"Old User")
 
Then, you can count New and Old User group by the month.

Actually I solved it this way: 

Jugadores =
SELECTCOLUMNS(
FILTER(NATURALINNERJOIN(users,game_entries),[private]=0&&[entry_currency]="real"),
"user id",
users[id],
"date created",
INT(FORMAT(users[created],"YYYYmm")),
"date game",
FORMAT(game_entries[_game_date],"YYYY-mm"),
"difference",
INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")),
"new",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) =0,game_entries[user_id],BLANK()),
"old",
IF(INT(FORMAT(game_entries[_game_date],"YYYYmm")) - INT(FORMAT(users[created],"YYYYmm")) >0,game_entries[user_id],BLANK()),
"game id",
game_entries[game_id]
)


kentyler
Solution Sage
Solution Sage

potentially you have multiple games and multiple users

what is the field (or fields) that links users to games





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


That is correct.
A user can play several games.

I link them with the user id. 

 

Games: 

game_id

game.user_id
game.date_created

 

Users:
user_id
user.created

 

What Im trying to visualize in a table is: 

Total users that played games in a certain month 

Total new users that played games in a certain month (users created in that month)

Total old users that played games in a certain month (users created in past months)

your first visualization can be done by just adding the fields user / game to a matrix and use a count of usergamesplayed id to count the games

 

the second visualization will be to get the date the user was created from the user table, you can do this by using the Related() function which will give you access to the user record for any usergameplayed, and then to get the date created from the game table. you can then compare the 2 dates and if they are the same its a new user.

You can use the same measure but when you compare if the dates are not the same its an old user, since if a user plays a game in a month they either where created in that month, or in some previous month





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


I assume you also have a table UserGamePlayed,

with user id, game id and date

 

that would be your fact table

user table, with user id and date created would be a dimension in a 1 to many relationship to user id in UserGamePlayed

Game table with game id and date created would also be a dimension





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Yup! The problem is to come up with the syntax of the query haha... 

you should add a calendar table to be a dimension that you could link to the date in UserGamesPlayed, and one for user and game tables as well....so you don't have to calculated the month for the date, but can get it from the calendar table

 

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


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.