Based on Slicer selection month, the Head count of users should be shown in previous 6month months

[sagarsahoo_123]

Hi All,

Hope all are doing good. I need your support to get the required result in Power BI. In below i am posting the sample data set and expected result. Please help in achiveing this.

Month		UserID		Project Code
Jan'24		AA		C123
Jan'24		AB		C234
Jan'24		AC		C156
Feb'24		AA		C123
Feb'24		AB		C234
Feb'24		AD		C456
Mar'24		AA		C123
Mar'24		AB		C234
Mar'24		AC		C456
Apr'24		AA		C123
Apr'24		AB		C234
Apr'24		AC		C456

The above is the sample data set in which Month and user ID and their respective Project code are given. Based on Slicer selection month, i need to display the total HC and User with same project code HC to be shown in table. Expect result 

Expected Result		Jan'24	Feb'24	Mar'24		Apr'24
Head Counts		3	2	2		3
Head Counts Same Project		2	2	2		3

This is the result i am expecting when i put a slicer and select the Apr'24 month. In Apr'24 there are total 3 users, Hence Head Count table is showing 3 for Apr'24 and for same set up users which are availble for Apr'24, from them how many are present in the previous 4 months. If you look at the data in Mar'24 2 users are present from Apri'24 set of users and it is extended to show for previous 4months. Apr'24 is selected in slicer and hence all users present in Apr'24 are choosen as set up users and from them how many are present in the previous month, those numbers are showing in the Head Count table. Similarly i want to bring the "Head counts with same project code" based on slicer selection month. When Apr'24 is selected, by default no of user having project code  should be shown in "Head Counts Same project" under Apr'24 table. Hence this is showing 3 and for prevous month it will compare the project code of Apr'24 vs Mar'24 month. And for those set of users if project code is matching compare to mar'24 with Apr'24 , then that number should be shown in "Head count same project" under Mar'24 table. In this way previous 4 months count should show. If you look at the Apr'24 user project code with Jan'24 project code, though there  same 3 users are present but in Jan'24 only 2 project code are matching with Apr'24 for those set of users. UserID "AC" has different project code in the month of Jan'24 compare to Apr'24. Hence the number in "Head count same project" number for Jan'24 is showing as 2.

Please help in achieving the above result. Any support on this would be highly appreciated. 

[sagarsahoo_123]

Hi @lbendlin ,

But here my requirement is based on the month selection, from the users present in that month, how many are present in other months. Ex:- If April, there are 3 users in total, from these 3 same users, what is number in other months. So in the given example, suppose i have selected April month in the slice. So in April month, AA,AB,AC user were present. So head count for April is 3. From these AA,AB,AC all 3 are present for March month but in Feb only AA and AB were present.

 SO March month head count is 3 whereas for Feb, it is 2.

[lbendlin]

You can use INTERSECT to get the desired number.

[lbendlin]

Consider using a graphical solution instead.

this will show you how projects are covered over the months and if there was a change in the User ID  (which only happened for the C456 project)