Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.