Forum Discussion

Pbiuserr's avatar
Pbiuserr
Post Prodigy
4 years ago
Solved

Measure with dates

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


Need Help
  • Pbiuserr's avatar
    Pbiuserr
    4 years ago

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Pbiuserr
      Post Prodigy
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.