Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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?
@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.
Hi Amit
Thank you again for helping me out. Please check below the date column I have created and is it correct?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |