create a dax measure which counts values on a date which are not on a previous date

[EnricMacarron]

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

[EnricMacarron]

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

[Jihwan_Kim]

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 ) )

[EnricMacarron]

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

[Jihwan_Kim · Accepted solution]

Hi,

I think mine looks different than yours.

Please check the below picture and the attached pbix file.

[EnricMacarron]

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

[Jihwan_Kim]

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.

[EnricMacarron]

This looks great. Thanks a lot Kim

[Jihwan_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 ) )