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

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