Week over Week Engagement based on last Logged In Datetimestamp

[MSW]

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! 

[amitchandak · Accepted solution]

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

Then 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8