Active Employees for Previous Month vs Previous Year/Month

[aellison]

I am trying to get a visual completed that looks like the following:

Personnel	2019-Nov	2018-Nov	Difference Count	Difference Percent
Local Employees	1024	865	159	18.38%
Remote Employees	5349	5184	165	3.18%

From data that looks like the following:

Personnel	Type	HireDate	TerminationDate
Steve	Local	1/1/2018
Jim	Local	2/5/2018	2/1/2019
Jill	Remote	3/6/2018
Bill	Remote	2/1/2019	5/1/2019
Debbie	Local	5/1/2019

I've tried to create this multiple ways and having no success. Can someone help me out with how to get this grid created in PowerBI?

Thank you,

[v-lid-msft · Accepted solution]

Hi @aellison ,

We can create following measures and use them in table visual to meet your requirement:

LastMonth = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [HireDate]
            < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

LastMonthOfLastYear = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [HireDate]
            < DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

Difference Count = [LastMonth] - [LastMonthOfLastYear]

Difference Percent = DIVIDE([Difference Count] , [LastMonthOfLastYear] , 0)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[v-lid-msft · Accepted solution]

Hi @aellison ,

We can create following measures and use them in table visual to meet your requirement:

LastMonth = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [HireDate]
            < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

LastMonthOfLastYear = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [HireDate]
            < DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

Difference Count = [LastMonth] - [LastMonthOfLastYear]

Difference Percent = DIVIDE([Difference Count] , [LastMonthOfLastYear] , 0)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[aellison]

v-lid-msft, your solution does solve the issue that I asked for, but I do want to leave this open a little longer for the part that I failed to mention. I would like this same action to happen when I select a date from a slicer, filter, etc. So if I wanted to run the same metric as if it was July of 2018, then my results would be to compare June 2018 to June 2017 ... (filter or slicer could also be the the month end). I basically just need to select a point-in-time for the result set.

Regardless, thank you very much for your solution and it will be marked as accepted ...

[v-lid-msft · Accepted solution]

Hi @aellison ,

Sorry for late reply, we can create a spreate date table as silcer:

SlicerDateTable = CALENDAR(MIN(MIN('Table'[HireDate]),MIN('Table'[TerminationDate])),Max(Max('Table'[HireDate]),MAX('Table'[TerminationDate])))

Then we can use create two measures to meet your requirement:

LastMonthOfLastYearOfSelectedDay = 

var SelectedDay = MAX('SlicerDateTable'[Date])
return
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [HireDate]
            < DATE ( YEAR ( SelectedDay ) - 1, MONTH ( SelectedDay ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( SelectedDay ) - 1, MONTH ( SelectedDay ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

LastMonthOfSelectedDay = 
var SelectedDay = MAX('SlicerDateTable'[Date])
return 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        [HireDate]
            < DATE ( YEAR (SelectedDay ), MONTH ( SelectedDay ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( SelectedDay ), MONTH ( SelectedDay ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,

[aellison]

@v-lid-msft  ... what I ended up with is VERY close to what you replied back with here. The only real difference is instead of using MAX, I used SELECTEDVALUE to force the user to select one specific date instead of a range. 

Counts of Previous Month:= 
VAR selected_date = SELECTEDVALUE('Date'[Date])
RETURN
CALCULATE (
    DISTINCTCOUNT('psa vw_PersonnelCombined'[EmpId]),
    FILTER (
        'psa vw_PersonnelCombined',
        [HireDate]
            < DATE ( YEAR ( selected_date ), MONTH ( selected_date ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( selected_date ), MONTH ( selected_date ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

and

Counts of Previous Year-Month:= 
VAR selected_date = SELECTEDVALUE('Date'[Date])
RETURN
CALCULATE (
    DISTINCTCOUNT('psa vw_PersonnelCombined'[EmpId]),
    FILTER (
        'psa vw_PersonnelCombined',
        [HireDate]
            < DATE ( YEAR ( selected_date ) - 1, MONTH ( selected_date ) - 1, 1 )
            && OR (
                [TerminationDate] >= DATE ( YEAR ( selected_date ) - 1, MONTH ( selected_date ), 1 ),
                ISBLANK ( [TerminationDate] )
            )
    )
)

Thank you again for your continued assistance. It is very much appreciated.