Released and Joined Employees count

[Powerful]

Hi,

I'm new to PowerBI DAX. I have connected data source to a folder where list of employees data dump is placed on regular basis (not daily).  Table name called Emp Data, columns are Data Dump Date, Emp ID, Emp Name and so on. I have created calendar table already in data model.

After appending different data files from folder it looks like as below:

Data Dump Date	Emp ID	Emp Name	Project Code
3/14/2022	12345	Ramesh	ABC123
3/14/2022	54321	Suresh	XYZ123
3/14/2022	11223	Mahesh	ABC123
3/14/2022	12342	Ganesh	XYZ123
3/14/2022	90876	Kailash	ABC123
3/14/2022	84782	Pawan	ABC123
3/14/2022	24729	Girish	XYZ123
3/17/2022	12345	Ramesh	ABC123
3/17/2022	54321	Suresh	XYZ123
3/17/2022	11223	Mahesh	ABC123
3/17/2022	12342	Ganesh	XYZ123
3/17/2022	90876	Kailash	ABC123
3/17/2022	23432	Tinu	ABC123
3/17/2022	63325	Minu	XYZ123
3/17/2022	25266	Ritu	ABC123

I'm trying to create a measure to find out count of employees released and joined in the next date.

Result should look like this:

3/14/2022	Released	Joined	3/17/2022
7	2	3	8

Released: 2 as Emp ID 84782, 24729 are missing in the next date.

similarly Joined: 3 as Emp ID 23432,63325,25266 were not there in the previous date.

Appreciate any help in this.

Thanks.

[v-zhangti · Accepted solution]

Hi, @Powerful 

You can try the following methods.

Measure:

Count1 = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Emp ID] = SELECTEDVALUE ( 'Table'[Emp ID] )
            && [Data Dump Date] <= SELECTEDVALUE ( 'Table'[Data Dump Date] )
    )
)

Count2 = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Emp ID] = SELECTEDVALUE ( 'Table'[Emp ID] )
            && [Data Dump Date] >= SELECTEDVALUE ( 'Table'[Data Dump Date] )
    )
)

Joined = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Data Dump Date] = MAX ( 'Table'[Data Dump Date] )
            && [Count1] = 1
    )
)

Released = 
CALCULATE (
    COUNT ( 'Table'[Emp ID] ),
    FILTER (
        ALL ( 'Table' ),
        [Data Dump Date] = MIN ( 'Table'[Data Dump Date] )
            && [Count2] = 1
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Powerful]

Hi @v-zhangti,

Thanks this is working fine.

I have more than 2 dates in my data.

I have put date slicer also. I'm trying to do this for only 2 dates as you have done, those 2 dates should come from slicer MIN and MAX. I trying to modify the measures to get the results for more than 2 dates but unable to do it. Please help me..

Appreciate your support!!

[Powerful]

Thanks a lot @v-zhangti 

This I was looking for, it is working.

[PhilipTreacy]

Hi @Powerful 

There isn't enough information to help you.  Please read this

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...

Can you please supply some sample data and an example of the result(s) you are looking for.

Regards

Phil

[Powerful]

Thanks Phil,

I added few more details with sample data.

Please help!