cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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

Many thanks!

Enric

1 ACCEPTED SOLUTION
Super User

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.

8 REPLIES 8
Regular Visitor

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

Super User

Hi,

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.

Regular Visitor

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

Super User

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.

Regular Visitor

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

Super User

Hi,

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.

Regular Visitor

This looks great. Thanks a lot Kim

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors