Forum Discussion
Released and Joined Employees count
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.
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.
5 Replies
- PhilipTreacy
Super User
Hi Powerful
There isn't enough information to help you. Please read this
Can you please supply some sample data and an example of the result(s) you are looking for.
Regards
Phil
Thanks Phil,
I added few more details with sample data.
Please help!
v-zhangtiCommunity Support
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.
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!!
