Forum Discussion

booooosevic's avatar
booooosevic
Frequent Visitor
7 years ago
Solved

How to calculate weekly active users?

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

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      booooosevic
      Frequent Visitor
      7 years ago

      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_keydate_keytime_keysession_idpage_key
      219805708814390e540f02-b7db-46f2-b310-0cb5a66b40d448
      219805708814390e540f02-b7db-46f2-b310-0cb5a66b40d47
      219805708814380e540f02-b7db-46f2-b310-0cb5a66b40d413
      219805708814380e540f02-b7db-46f2-b310-0cb5a66b40d47
      219805708814370e540f02-b7db-46f2-b310-0cb5a66b40d43
      219805708814370e540f02-b7db-46f2-b310-0cb5a66b40d47
      219805708814370e540f02-b7db-46f2-b310-0cb5a66b40d44
      219805708814370e540f02-b7db-46f2-b310-0cb5a66b40d47
      219805708814360e540f02-b7db-46f2-b310-0cb5a66b40d44
      219805708814360e540f02-b7db-46f2-b310-0cb5a66b40d47
      219805708814350e540f02-b7db-46f2-b310-0cb5a66b40d47
      2874387088142749a84088-26b5-4c5a-9fa5-50946e9cec2942
      2874387088142749a84088-26b5-4c5a-9fa5-50946e9cec2919
      2874387088142549a84088-26b5-4c5a-9fa5-50946e9cec2919
      2874387088142549a84088-26b5-4c5a-9fa5-50946e9cec2942

       

      The final effect I want is that:

      datedaily_active_user_cntweekly_active_user_cnt
      2019/6/41417  
      2019/6/31002  
      2019/6/2795 2839 
      2019/6/1836 2839 
      2019/5/31994 2839 
      2019/5/301023 2839 
      2019/5/29967 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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.