Measure with dates

[Pbiuserr]

Hello. Trying to calculate headcount. Basically it means I am trying to count the rows for the period if the hiredate is less than the date and the termdate is greater than date OR if the termdate is blank.

It captures properly blank on termination dates, but If I click for instance a employee started job at 2017 and terminated in 2021, he is not seen in headcount for 2017-2018-2019-2020 as supposed to. Down below is my calculation. Thank you for your advices

Hcount =
CALCULATE (
    [Count of Employees], (count of emp ID)
    FILTER (
        Dataset,
        (
            Dataset[StartDate] >= MAX ( 'Calendar'[Date] )
                && Dataset[TerminationDate] < MAX ( 'Calendar'[Date] )
        )
            || ISBLANK ( Dataset[TerminationDate] )
    )
)

[Anonymous]

I think you have your signs backwards- it seems like it should be Dataset[StartDate]<=MAX ( 'Calendar'[Date] )<Dataset[TerminationDate], but you have Dataset[TerminationDate]<MAX ( 'Calendar'[Date] )<=Dataset[StartDate]

[Pbiuserr]

Hello @Anonymous 
You are right - late hours of working. Now it looks a bit better, although I can see that if I filter for instance ID550 from that table, he'll be visible in chart only in 2018 (as his StartDate year), not in 2019, 2020 etc. Can't see him in headcount for these years

[Anonymous]

Hi  @Pbiuserr，

Did you get the solution for your thread? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Otherwise,  you can update the measure [Hcount] as below:

Hcount =
VAR _seldate =
    MAX ( 'Calendar'[Date] )
RETURN
    SUMX (
        Dataset,
        IF (
            Dataset[StartDate] <= _seldate
                && OR (
                    Dataset[TerminationDate] >= _seldate,
                    ISBLANK ( Dataset[TerminationDate] )
                ),
            1,
            BLANK ()
        )
    )

You can also refer the solution in the following thread to get it.

count rows containing blank and date values for a given month

Best Regards

[Pbiuserr · Accepted solution]

This solved my problem:

Headcount =
CALCULATE (
    COUNTROWS ( dataset ),
    FILTER ( VALUES ( dataset[start] ), dataset[start] <= MAX ( 'Calendar'[Date] ) ),
    FILTER (
        VALUES ( dataset[term] ),
        OR ( dataset[term] >= MIN ( 'Calendar'[Date] ), ISBLANK ( dataset[term] ) )
    )
)