Distinct Count from filtered table by per category max value records.

[LahiruFernando]

Hi,
I am a novice to Power bi and expect your expertise assistance to solve below.
I have two tables, one is device data and the other is date table. Relationship between two tables is start date from device table and date from date table.

Device Table

rank_date	user_id	start_date	end_date	device
1	100001	10/1/2020	10/1/2022	x
2	100001	20/2/2020	20/2/2022	y
3	100001	1/5/2021	1/6/2023	x
1	100002	20/1/2020	20/1/2022	y
1	100003	15/1/2020	15/1/2022	y
2	100003	14/05/2020	14/05/2022	x
1	100004	1/3/2021	1/3/2023	x

If taking the user id 100001 to explain the logics to calculate ,

Final output should be like below table which Year and month is from date table.

(to get below table, always need to consider the record of maximum deviceTable[rank_date] for that particular user filtered first for considered date period)

1. Active users - 

distinct count of users, where deviceTable[start_date] <= endOfPeriod  && deviceTable[end_date] >= startOfPeriod

2. active_x_devices

distinct count of users, where deviceTable[start_date] <= endOfPeriod  && deviceTable[end_date] >= startOfPeriod, and if the deviceTable[device] = "x"

2. active_y_devices

distinct count of users, where deviceTable[start_date] <= endOfPeriod  && deviceTable[end_date] >= startOfPeriod, and if the deviceTable[device] = "y"

Here the user's 100001 device "y" is active after 20/2/2020 his device "x" is no more considered as active as we always take the maximum deviceTable[rank_date] per user for the considered period.

Year	Month	Active Users	active_x_devices	active_y_devices
2020	January	1	1
2020	Ferbruay	1		1
2020	March	1		1
2020	April	1		1
2020	May	1		1

Appreciate if anyone could help. 

Thanking in advance.

[amitchandak]

@LahiruFernando , Try like this

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[LahiruFernando]

Hi @amitchandak ,

Thank you for your reply, of course I can apply the logic in the meterials you send to capture active and inactive users of the selected period. can you pls help me out to extend the formula to capture active x device users and active y device users as this is the critical one.

Here i need to consider the record of the user having maximum rank for that specific period.

Thank you