Forum Discussion

tally's avatar
tally
Regular Visitor
4 years ago

count based on date

Hello, 


I am new to PowerBI and I am trying to create two measures, one for weekly active users, and one for monthly active users.

I want the two measures to be calculated on a daily basis, so I can see them on a line chart as well.

I managed to do it partially with the following measure:

 

 

wau = 
var CurrentDate= SELECTEDVALUE('growth_sum'[Date])
return
    COUNTROWS(CALCULATETABLE(growth, filter(growth, DATESINPERIOD(growth[event date], CurrentDate, -7, DAY))))

 

 


the growth table contains all the things the user did.
If I use this measure it calculates the DAILY active users, so I need to add a filter of the top 7 from the latest dates, so I can see the WAU as a number but not as a linechart.

Can you please help me?
Thank you so much!
Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    tally as a best practice when you are working with time intelligence you should always add a calendar dimension in your model and then use that for all time intelligence DAX function. You can check my youtube playlist on time intelligence and also how to add a calendar table, and then recreate your measures using date from the calendar table and see if that fixes the issue.  https://youtube.com/playlist?list=PLiYSIjh4cEx2FkuGkngYruS0wm8MYFsoi

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    • tally's avatar
      tally
      Regular Visitor
      4 years ago

      Thank you so much for replying!
      I already created this table and I tried doing the same measure with it but the result is the same...
      Do you have any other ideas why it won't work? or any other solutions for this measure?

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi tally ,

     

    Please try the measure.

     

    wau = 
CALCULATE( COUNTROWS(growth),DATESINPERIOD(growth_sum[Date], MAX(growth[event date]), -7, DAY))

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • tally's avatar
      tally
      Regular Visitor
      4 years ago

      Hey,

       

      Thank you so much for your time but it still not work.

       

      The problem is that it still gives me all the dated and not the dates in this period.

      Basically, I want a rolling WAU, so every day I will know the updated WAU for the last week..