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 c...
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

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