Forum Discussion

lct14558's avatar
lct14558
New Member
10 years ago

date no weekly hierarchy

Hi all, first post here!

 

New to PowerBI here, avid user of Python notebook and Excel. 

 

I want to do something very simple. 

 

I have two columns of data. First column has Names, second column has a date-time of when the person has logged into a system, and a person can log into the system multiple times a day so there can be many rows of the same name but with a different date. Data spans over 2 years.

 

I want to know "how many UNIQUE users logged into a system on a weekly basis?" 

Basically, I need a unique count of records by Name every 7 days. Note that I'm not looking for "first/second/third" week of every month because each month has a different number of weeks. I'm strictly counting every 7 days. 

 

Not sure how I can accomplish this in PowerBI. Thank you in advance!

Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi Lct15558,

     

    According to your description, you need to count the distinct users for each week in each month, right?

     

    If that is the case, you can create a calculated column to get the week number in each month, and create a calculated measure to get the distinct users in that month. Here is the sample DAX expression for you reference.
    Week = YEAR(WeekHierarchy[Date])&RIGHT("00"&MONTH(WeekHierarchy[Date]),2)&"-"&CEILING(DAY(WeekHierarchy[Date])/7,1)
    CountForWeek = CALCULATE(DISTINCTCOUNT(WeekHierarchy[UserID]),ALLEXCEPT(WeekHierarchy,WeekHierarchy[Week]))

     

    Regards,

    Charlie Liao

  • GilesWalker's avatar
    GilesWalker
    Icon for Skilled Sharer rankSkilled Sharer
    10 years ago

    lct14558 In order to do a unique count of the names you can use the following measure:

     

    Unique logins:= DISTINCTCOUNT(Table[Names])

     

    This will count the distinct user names in the column. In order to see what the number is every seven days there are a couple ways of doing this. Personnally I would use my DateKey tables Week column, this way the measure will count the unique user in each week. However it depends on how you want to display your data.

     

    Thanks,

     

    Giles

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      10 years ago

      lct14558 Try this Measure... This should give you the Trailing 7 Day Distinct Count

      You'll need a Calendar Table and you may need to separate date and time fields... Let me know if it works!

      Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    DATESINPERIOD ( CalendarTable[Date], LASTDATE ( CalendarTable[Date] ), -7, DAY )
)