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

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.

    • Pbiuserr's avatar
      Pbiuserr
      Post Prodigy
      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] ) )
          )
      )