Previous Month Headcount

[yaman123]

Hi all, 

I am trying to calculate previous months headcount. I have the below to calculate the headcount per month 

Headcount =

var selectedDate = MAX('Date'[Date])

RETURN

CALCULATE(DISTINCTCOUNT('Headcount by Function'[EMP_NO]),

'Headcount by Function'[VALID_FROM] <= selectedDate, OR(ISBLANK('Headcount by Function'[VALID_TO]),

'Headcount by Function'[VALID_TO] > selectedDate))

I am looking for previous months headcount from the month year chosen from the slicer. If i select Oct2020, i will need to dsiplay Sept2020 headcount 

Thanks

Yasir

[v-janeyg-msft · Accepted solution]

Hi, @yaman123 

According to your description, if you have month and year column in date table, you can try like this:

Headcount =
VAR year =
    SELECTEDVALUE ( 'Date'[year] )
VAR month =
    SELECTEDVALUE ( 'Date'[month] )
VAR date =
    DATE ( year, month, 1 )
VAR previousfirstday =
    EDATE ( date, -1 )
VAR previouslastday =
    EOMONTH ( date, -1 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Headcount by Function'[EMP_NO] ),
        'Headcount by Function'[VALID_FROM] >= previousfirstday
            || ISBLANK ( 'Headcount by Function'[VALID_TO] ),
        'Headcount by Function'[VALID_TO] <= previouslastday
    )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[themistoklis]

@yaman123 

Can you try the following formula.

Previous Month Count = CALCULATE(DISTINCTCOUNT('Headcount by Function'[EMP_NO]),DATESMTD(DATEADD('Date'[Date],-1,MONTH)))

OR

Previous Month Count = CALCULATE(DISTINCTCOUNT('Headcount by Function'[EMP_NO]) PREVIOUSMONTH('Date'[Date]))

[yaman123]

Hi @themistoklis 

This doesnt work unfortunately. It brings back all employees even those who have left. I only need to show employees who were with the business last month 

[themistoklis]

@yaman123 

Do you want selecteddate to be the last date (31/01/2021) of previous month? 

If this is the case then try the following formula:

Headcount =

var selectedDate = EOMONTH(TODAY(),-1)

RETURN

CALCULATE(DISTINCTCOUNT('Headcount by Function'[EMP_NO]),

'Headcount by Function'[VALID_FROM] <= selectedDate, OR(ISBLANK('Headcount by Function'[VALID_TO]),

'Headcount by Function'[VALID_TO] > selectedDate))