Beginner level - Measure combined with date filter

[StefRenneboog91]

Hello,

First of all thanks already for trying to help me.

As you can see we have a table with these fields : 

PERNR

BEGDA

ENDDA

And a column I created myself (Z2Date)

This Z2Date is showing the date when someone joined the company.

The BEGDA & ENDDA date are showing the validity of the record. 

So in this case the validity of this record is starting 27/10/2020 (BEGDA) until 31/12/9999 (ENDDA) where the person joined the company on 27/10/2020 (Z2Date)

Now I want to be able to create a graph giving the "New Joiners" in time.

So on X axis I want the a date (YEAR / MONTH / ..) and on Y axis the count of the new joiners.

I also have a Date table where BEGDA/ENDDA & Z2Date are linked to Date in my Date table.

I guess now I need to create a measure "New Joiners" but I can't figure out how.

The measure I have until now (which is wrong) is this :

NewJoiner =

COUNTROWS(

Filter(

PA0041,

PA0041[BEGDA] <= TODAY() &&

PA0041[ENDDA] >= TODAY()

)

)

Thanks a lot already for the effort.

KR,

Stef

[AlB · Accepted solution]

Hi @StefRenneboog91 

If you have an active relationship between DateTable[Date] and PA0041[Z2Date], this should do it:

New joiners =

DISTINCTCOUNT( PA0041[PERNR] )

were it's assumed that PERNR is the unique id identifying an employee

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers