Count - Date Range

[kh0050]

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	DayAccessed	Count 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

[v-yiruan-msft · Accepted solution]

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]

@v-yiruan-msft -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? 

[amitchandak]

@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]

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" ))