Forum Discussion

kh0050's avatar
kh0050
Frequent Visitor
5 years ago
Solved

Count - Date Range

Hi All
See the table below, I need to count the number of days access ( using DaysAccessed) by each student in the last 3 months or 90days. Can you help me either to create a measure or calculated column DAX expressions? 

 

UserId    DayAccessedCount of Days Access
kh0078   2020-12-13T00:00:00.0000000Z 
SB7655   2021-05-14T00:00:00.0000000Z 
kh0078   2021-01-06T00:00:00.0000000Z 
SB7655   2021-05-06T00:00:00.0000000Z 
kh0078   2021-05-16T00:00:00.0000000Z 
SB7655   2021-05-13T00:00:00.0000000Z 
kh0078   2021-03-12T00:00:00.0000000Z 
kh0078   2021-04-11T00:00:00.0000000Z 

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi kh0050 ,

    You can create a measure as below:

     

    Count of Days Access = 
CALCULATE (
    COUNT ( 'Table'[UserId] ),
    FILTER (
        'Table',
        'Table'[DayAccessed]
            >= TODAY () - 90
            && 'Table'[DayAccessed] <= TODAY ()
    )
)

     

    Best Regards

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    kh0050 , with help from a date table, joined day accessed

     

    Rolling 3 = CALCULATE(count(Table[day accessed ]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • kh0050's avatar
      kh0050
      Frequent Visitor
      5 years ago

      Hi Amit 

       

      Thank you again for helping me out. Please check below the date column I have created and is it correct? 

       

      Date =
      ADDCOLUMNS (
      CALENDARAUTO(),
      "Year", YEAR ( [DATE] ), "Month", FORMAT( [DATE],"mmmm" ))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi kh0050 ,

    You can create a measure as below:

     

    Count of Days Access = 
CALCULATE (
    COUNT ( 'Table'[UserId] ),
    FILTER (
        'Table',
        'Table'[DayAccessed]
            >= TODAY () - 90
            && 'Table'[DayAccessed] <= TODAY ()
    )
)

     

    Best Regards

    • kh0050's avatar
      kh0050
      Frequent Visitor
      5 years ago

      Anonymous -Thank you for your help. What if the student is enrolled in multiple courses and I need to have reports of his/her visits to each course in the last 90days or 3 months then how I will do that please?