Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Again I would like to ask you for your help.
I have one table with empid and rise date looking as follows:
EmpID RiseDate
1 01 January 2022
2 01 January 2022
3 01 February 2022
2 01 March 2022
3 01 March 2022
4 01 April 2022
5 01 April 2022
With this I would like to have distinct count of people for each year. So in Jan I would like to have as a result 2, in Feb 3, in March 3 again because these are same people based on empid getting rise.
And then I have second table telling me if some1 is no longer working for us:
EmpID TerminationDate
3 01 April 2022
So for the results for April I would like to have 4, because 2 new people got rise but one of the person who got rise before april in same year is no longer with us, so he/she should not be counted anymore.
The solution should work only for each year separately as mentioned before.
I would be much appreciated for any tips how to solve this.
Thank you very much in advance for your help.
Solved! Go to Solution.
Hi,
Thank you for your explanation.
Please check the below and the attached file if it suits your requirement.
Thank you.
Distinct count in a year: =
VAR _emplist =
CALCULATETABLE (
SUMMARIZE ( Rise, Employee[EmpID] ),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
RETURN
COUNTROWS (
CALCULATETABLE (
SUMMARIZE ( Rise, Employee[EmpID] ),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
)
- COUNTROWS (
CALCULATETABLE (
FILTER (
SUMMARIZE ( Termination, Employee[EmpID] ),
Employee[EmpID] IN _emplist
),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
I suggest having a dim-date table like below.
Distinct count in a year: =
COUNTROWS (
CALCULATETABLE (
SUMMARIZE ( Rise, Employee[EmpID] ),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
)
- COUNTROWS (
CALCULATETABLE (
SUMMARIZE ( Termination, Employee[EmpID] ),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, Thank you for your respond.
The solution will not work in situation when in termination table will be more rows but they will not be present in rise table i.e.
EmpID TerminationDate
3 01 April 2022
6 01 April 2022
Solution should cross-check if person from termination was present in respective year in rise table and then subtrack it from result.
Hi,
Thank you for your explanation.
Please check the below and the attached file if it suits your requirement.
Thank you.
Distinct count in a year: =
VAR _emplist =
CALCULATETABLE (
SUMMARIZE ( Rise, Employee[EmpID] ),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
RETURN
COUNTROWS (
CALCULATETABLE (
SUMMARIZE ( Rise, Employee[EmpID] ),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
)
- COUNTROWS (
CALCULATETABLE (
FILTER (
SUMMARIZE ( Termination, Employee[EmpID] ),
Employee[EmpID] IN _emplist
),
'Date'[Date] <= MAX ( 'Date'[Date] ),
'Date'[Year] = MAX ( 'Date'[Year] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you for your help.
Really appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |