Calculation based on multiple values from slicer

[Lubeno78]

Hello,

I'm trying to get number of active employees per month(s). I have folowing formula:

ActiveEmployees per month = 

VAR _EndOfMonth =

SELECTEDVALUE(EndMonth[EndMonth] ) 

VAR _calculation =

CALCULATE(

DISTINCTCOUNT( HC[ID] ),

Filter(

HC,

HC[Starting Date] < _EndOfMonth

&& HC[Ending Date] >= _EndOfMonth 

)

)

RETURN

IF ( ISBLANK ( _calculation ), 0, _calculation ) 

This formula works perfectly, except one thing - when I choose several months from slicer, result is always 0 (zero).

What to change to get correct calculation per one month + correct calculation when several month were chosen?

Thank You!

[tamerj1 · Accepted solution]

Hi @Lubeno78 
Replace SELECTEDVALUE with MAX

ActiveEmployees per month =
VAR _EndOfMonth =
    MAX ( EndMonth[EndMonth] )
VAR _calculation =
    CALCULATE (
        DISTINCTCOUNT ( HC[ID] ),
        FILTER (
            HC,
            HC[Starting Date] < _EndOfMonth
                && OR ( ISBLANK ( HC[Ending Date] ), HC[Ending Date] >= _EndOfMonth )
        )
    )
RETURN
    IF ( ISBLANK ( _calculation ), 0, _calculation )

[Lubeno78]

...hahaha, so easy! 🙂 Thank you very much @tamerj1 !

[tamerj1]

@Lubeno78 

No I think this is wrong it will only return the last month. I will update tou with the correct solution 

[tamerj1 · Accepted solution]

@Lubeno78 
I hope this will do the trick. Create 2 measures 

ActiveEmployees per month =
VAR _EndOfMonth =
    MAX ( EndMonth[EndMonth] )
VAR _calculation =
    CALCULATE (
        DISTINCTCOUNT ( HC[ID] ),
        FILTER (
            HC,
            HC[Starting Date] < _EndOfMonth
                && OR ( ISBLANK ( HC[Ending Date] ), HC[Ending Date] >= _EndOfMonth )
        )
    )
RETURN
    IF ( ISBLANK ( _calculation ), 0, _calculation )

ActiveEmployees RT =
VAR StartDate =
    MIN ( EndMonth[EndMonth] )
VAR EndDate =
    MAX ( EndMonth[EndMonth] )
RETURN
    CALCULATE (
        [ActiveEmployees per month],
        EndMonth[EndMonth] >= StartDate,
        EndMonth[EndMonth] <= EndDate
    )

[Lubeno78]

Thank You again, @tamerj1 !! 

[tamerj1 · Accepted solution]

Hi @Lubeno78 
Replace SELECTEDVALUE with MAX

ActiveEmployees per month =
VAR _EndOfMonth =
    MAX ( EndMonth[EndMonth] )
VAR _calculation =
    CALCULATE (
        DISTINCTCOUNT ( HC[ID] ),
        FILTER (
            HC,
            HC[Starting Date] < _EndOfMonth
                && OR ( ISBLANK ( HC[Ending Date] ), HC[Ending Date] >= _EndOfMonth )
        )
    )
RETURN
    IF ( ISBLANK ( _calculation ), 0, _calculation )