Forum Discussion
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 _calcula...
Hi Lubeno78
Replace SELECTEDVALUE with MAXActiveEmployees 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
I hope this will do the trick. Create 2 measuresActiveEmployees 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 )
tamerj1
Community Champion
Community ChampionNo I think this is wrong it will only return the last month. I will update tou with the correct solution
tamerj1Community Champion
Community ChampionLubeno78
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
)
