March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi everyone,
I'm trying to create a measure which counts the number of employees IDs based on a date column which are not found on the previous day to that date (i.e. in other words, new hirings of employees).
Could you please help me? I've tried many ways already.
Many thanks!
Enric
Solved! Go to Solution.
Hi,
I think mine looks different than yours.
Please check the below picture and the attached pbix file.
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 Kim,
This is very helpful. I've almost got it, with the exception that the formula seems to try to find the employee id not only on the previous day, but on any date prior to the base date (i.e. employee id on 23/01/2023 may not be found on 22/01/2023 but it can be found on 16/12/2022. It is still a new hiring on 23/01/2023 despite it has previously been on the Company before, however the formula returns the opposite). I only want to look at the inmmediate previous day to the base date, and consider that as a new hiring if employee id is not found
Hi,
Thank you for your reply.
If it only considers one day before, please try writing a measure something like below.
New employees count V2: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _currentemployeelist =
SUMMARIZE ( Data, Employee[Employee] )
VAR _prevemployeelist =
CALCULATETABLE (
SUMMARIZE ( Data, Employee[Employee] ),
'Calendar'[Date] = _currentdate-1
)
VAR _onlynewlist =
EXCEPT ( _currentemployeelist, _prevemployeelist )
RETURN
IF ( HASONEVALUE ( 'Calendar'[Date] ), COUNTROWS ( _onlynewlist ) )
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.
Thanks Kim for your reply.
I've adjuted the formula as per your comment, however does not seem to be working. Please see table below (it does not allow me to attach). Employee "D" on 03/01/2021 should be counted as 1, as it is not found on 02/02/2021. Also employee "A" should not be counted as 1, as it is found on 02/021/2021.
Any help would be really appreciated.
Thanks a lot
Hi,
I think mine looks different than yours.
Please check the below picture and the attached pbix file.
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 Kim,
One additional question, how do I create a new column within the 'Data' table instead of a measure (giving me the same info).?
I'm having an issue in my matrix table in the first column, i.e. date (selected with a slicer) as is not able to look at an earlier date than selected range, despite there is data. Therefore it shows me all list of employees as if they had been hired on the first seleceted date.
Thanks a lot.
Enric
Hi,
Thank you for your message.
I am not sure if I understood your question correctly, but please check the below picture.
I think it is still giving the numbers that you want.
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.
This looks great. Thanks a lot Kim
Hi,
I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
I hope the below can provide some ideas on how to create a solution for your datamodel.
New employees count: =
VAR _currentdate =
MAX ( 'Calendar'[Date] )
VAR _currentemployeelist =
SUMMARIZE ( Data, Employee[Employee] )
VAR _prevemployeelist =
CALCULATETABLE (
SUMMARIZE ( Data, Employee[Employee] ),
'Calendar'[Date] < _currentdate
)
VAR _onlynewlist =
EXCEPT ( _currentemployeelist, _prevemployeelist )
RETURN
IF ( HASONEVALUE ( 'Calendar'[Date] ), COUNTROWS ( _onlynewlist ) )
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
26 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
38 | |
29 | |
28 | |
20 | |
18 |