Forum Discussion

suegee's avatar
suegee
Regular Visitor
7 years ago
Solved

Employee Count based on Hire/Rehire date

Hi,  I am new to Power BI and tried to find something that was similar to what I was trying to do.  I didn't have much luck on this and appreciate any assistance.  I have data that has unique employe...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi suegee

     

    You may refer to below measure. 

    Measure  =
VAR currentDate =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( EE_List ),
        FILTER ( EE_List, EE_List[Year] = 2017 && EE_List[Date] <= currentDate )
    )

    Regards,

    Cherie

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi - Can you check this PBIX file is of any help:

https://files.fm/u/x85mwmey

 

Also, when I copy/pasted your date values, I got an error and I had to make some transformations to get them right. Please check if months and dates have been reversed for single digits. Else you can simply replace the entire date values with the correct ones.

suegee's avatar
suegee
Regular Visitor
7 years ago

Thanks Nikhil,  How would I write a measure that would only pick up the lastest number for each month in 2018 and a separate measure for 2017?  I see that you have an employee count that shows for each date, but I only want the latest number for that month.  Sorry, I am still learning how to write formulas here. 

 

I had used this formula based on another post and filtered by year, but I couldn't figure out how to get 2017 and 2018 separate from each other, so that I could write a measure for each in order to calculate the Year Over Year variance. 

2018 Active EE = 
VAR currentDate =
    MAX ( 'Date'[Date])
RETURN
    CALCULATE (
        COUNTROWS ( 'EE_List' ),
        FILTER (
            'EE_List',
            ( 'EE_List'[Hire_Date] <= currentDate)))

Thanks for helping me with this.

 

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi suegee

     

    You may refer to below measure. 

    Measure  =
VAR currentDate =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( EE_List ),
        FILTER ( EE_List, EE_List[Year] = 2017 && EE_List[Date] <= currentDate )
    )

    Regards,

    Cherie

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

      suegee you need to add calendar dimension in your model and that it become very easy. There are many post on how to calendar in your data model.

       

      Once calendar dimension is added, you will able to use year from that table to get total of each year.

       

      Let me know if you need further assistance.

    • suegee's avatar
      suegee
      Regular Visitor
      7 years ago

      Thanks Cherie,

       

      The only way I could get this to work is if I added second date calendar that had just 2017 dates in it, so that it could be the max and then it would work.   Not sure if that's the right way to do it.