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

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