Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago
Solved

Previous Month Headcount

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(DISTIN...
Need Help
Show and Tell
Tips and Tricks
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

yaman123's avatar
yaman123
Post Partisan
5 years ago

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's avatar
themistoklis
Community Champion
5 years ago

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))