How to calculate weekly active users?

[booooosevic]

The dau = DISTINCTCOUNT('behavior'[user_key])

The behavior table is related to a date dimension table, which has a date column.

the wau on 2019-06-03 should be the DISTINCTCOUNT of user_key from 2019-05-27 to 2019-06-02.

Any help would be appreciated!

EDIT:

I solved the problem by

Create a column "YearWeek" in the Date table that has the same value for each day within the same week (it could be a sequential integer or the date of the last day in the week). 
Then you write:

 weekly_active_user_cnt :=
    CALCULATE (
        DISTINCTCOUNT ( 'tb'[user_key] ),
        ALL ( 'Date' ),
        VALUES ( 'Date'[YearWeek] )
    )

Idea from Marco Russo http://disq.us/p/2298ke7

[v-juanli-msft · Accepted solution]

Hi @booooosevic 

It seems this problem is sloved.

I would suggest you to mark your answer as a solution so more people can find it.

---

@booooosevic wrote:

EDIT:

I solved the problem by

Create a column "YearWeek" in the Date table that has the same value for each day within the same week (it could be a sequential integer or the date of the last day in the week). 
Then you write:

 weekly_active_user_cnt :=
    CALCULATE (
        DISTINCTCOUNT ( 'tb'[user_key] ),
        ALL ( 'Date' ),
        VALUES ( 'Date'[YearWeek] )
    )

Idea from Marco Russo http://disq.us/p/2298ke7

---

Best Regards
Maggie

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

[v-juanli-msft]

Hi @booooosevic

You could try this measure

Measure = CALCULATE(DISTINCTCOUNT(Sheet3[user_key]),FILTER(ALLSELECTED('calendar'),'calendar'[Date]<=MAX('calendar'[Date])))

If it doesn't slove the problem, clear me about the following: 

---

@booooosevic wrote:

the wau on 2019-06-03 should be the DISTINCTCOUNT of user_key from 2019-05-27 to 2019-06-02.

---

user_key is active in 2019-5-27,28,29, 6-1,2

but inactive in 2019-5-30,31,

Should we include this user_key when calcuate the wau on 2019-06-03?

Best Regards
Maggie

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

[booooosevic]

@v-juanli-msft 

Hi Maggie:

Thanks for helping.

I'm sorry, but my description of expectations is not clear enough. In fact, we have a user behavior table(tb1) that records every behavior that a user has in our program.

The basic active user measure, active_user_cnt= DISCOUNTCOUNT('tb1'[user_key])

tb1:

user_key	date_key	time_key	session_id	page_key
219805	7088	1439	0e540f02-b7db-46f2-b310-0cb5a66b40d4	48
219805	7088	1439	0e540f02-b7db-46f2-b310-0cb5a66b40d4	7
219805	7088	1438	0e540f02-b7db-46f2-b310-0cb5a66b40d4	13
219805	7088	1438	0e540f02-b7db-46f2-b310-0cb5a66b40d4	7
219805	7088	1437	0e540f02-b7db-46f2-b310-0cb5a66b40d4	3
219805	7088	1437	0e540f02-b7db-46f2-b310-0cb5a66b40d4	7
219805	7088	1437	0e540f02-b7db-46f2-b310-0cb5a66b40d4	4
219805	7088	1437	0e540f02-b7db-46f2-b310-0cb5a66b40d4	7
219805	7088	1436	0e540f02-b7db-46f2-b310-0cb5a66b40d4	4
219805	7088	1436	0e540f02-b7db-46f2-b310-0cb5a66b40d4	7
219805	7088	1435	0e540f02-b7db-46f2-b310-0cb5a66b40d4	7
287438	7088	1427	49a84088-26b5-4c5a-9fa5-50946e9cec29	42
287438	7088	1427	49a84088-26b5-4c5a-9fa5-50946e9cec29	19
287438	7088	1425	49a84088-26b5-4c5a-9fa5-50946e9cec29	19
287438	7088	1425	49a84088-26b5-4c5a-9fa5-50946e9cec29	42

The final effect I want is that:

date	daily_active_user_cnt	weekly_active_user_cnt
2019/6/4	1417
2019/6/3	1002
2019/6/2	795	2839
2019/6/1	836	2839
2019/5/31	994	2839
2019/5/30	1023	2839
2019/5/29	967	2839

I could simply use a table visual with the active_user_cnt measure as the daily_active_user_cnt.

Now I'm wondering how to calculate the weekly_active_user_cnt. 

You may notice that the weekly_active_user_cnt is the same number in 2019/5/27-2019/6/2, since they are in the same calendar week.

And on 6/3 & 6/4 they are blank because the current week has not ended yet.

As you mentioned about the user_key is active in 2019-5-27,28,29, 6-1,2 but inactive in 2019-5-30,31, we just ignore this. So the weekly_active_user_cnt is not equal to the sum of the 7 days dau. That's not a problem to us.

Please feel free to let me know if you still have any other confusions. Thanks in advance.