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
No I think this is wrong it will only return the last month. I will update tou with the correct solution
- tamerj1
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 )