Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

Could you please help me? I've tried many ways already.

 

Many thanks!

 

Enric

 

1 ACCEPTED SOLUTION

Hi,

I think mine looks different than yours.

Please check the below picture and the attached pbix file.

Jihwan_Kim_1-1674528341443.png

 

 

Jihwan_Kim_0-1674528327194.png

 

 

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.


Go to My LinkedIn Page


View solution in original post

8 REPLIES 8
EnricMacarron
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

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.


Go to My LinkedIn Page


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.

 

Example 2.PNGExample 1.PNG

 

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.

Jihwan_Kim_1-1674528341443.png

 

 

Jihwan_Kim_0-1674528327194.png

 

 

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.


Go to My LinkedIn Page


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.

 

Jihwan_Kim_0-1674648203598.png

 

 

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.


Go to My LinkedIn Page


This looks great. Thanks a lot Kim

Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1674484836569.png

 

Jihwan_Kim_1-1674485035127.png

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors