Forum Discussion
Week over Week Engagement based on last Logged In Datetimestamp
Hello,
I am trying to figure out how to create a KPI that can measure engagement based on the total number of users and the last date of activity. Ideally would like to look at the total and difference week to week. I think I have the week over week date wondering how to create the measure to look to see if a user loged in the last week, count the number of users over the total numver of users.
| USER ID | Last Login |
| 1 | 10/1/2022 |
| 2 | 10/2/2022 |
| 3 | 10/3/2022 |
| 4 | 10/4/2022 |
| 5 | 9/4/2022 |
| 6 | 9/7/2022 |
| 7 | 10/1/2022 |
| 8 | 10/2/2022 |
| 9 | 8/30/2022 |
Therefore if I am looking at the week of 10/1/2022-10/7/2022 - I would have 6 users and the engagement would show 6/9 users
Any help on how to set this up would be greatly appreciated. Thank you!
MSW , You need have date table with following columns
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatThen you can have measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
1 Reply
MSW , You need have date table with following columns
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatThen you can have measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8