Forum Discussion

Lubeno78's avatar
Lubeno78
New Member
4 years ago
Solved

Calculation based on multiple values from slicer

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's avatar
    tamerj1
    4 years ago

    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 )
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
    )

     

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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 )
    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Lubeno78 

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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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
    )