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(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
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.

4 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    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'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))
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    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.