Forum Discussion

SofG's avatar
SofG
Frequent Visitor
6 years ago

Head count formula not working

Hi,   I wish to calcute number of contracts and FTE's. My employee data set looks like this:   Person ID                  Cost Center            Role                 Employment start date        ...
Need Help
Show and Tell
SofG's avatar
SofG
Frequent Visitor
6 years ago

Thank you!

 

Unfortunately I wasn't able to get "Periodic billing" code to work. Maybe it is because I already have a calendar table which I use to filter multiple other tables?

 

I created a sample of some modified data. I want to measure how many employees we are at a specific time. If I filter 2019, I want it to calculate to the latest date within that filter.

I would be really greatful if you could take a look at this data:

Sampla data in sharepoint

 

Thank you in advance!

 

Kind regards

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    HI, SofG 

    Please try this formula:

    measure = 
VAR tmpCalendar = ADDCOLUMNS('Dynamic Calendar',"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('People Data',"MonthYearBegin",VALUE(YEAR([Employment start date]) & FORMAT(MONTH([Employment start date]),"0#")),
                                      "MonthYearEnd",IF( [Employment end date]=BLANK(),VALUE(YEAR(MAX('Dynamic Calendar'[Date])) & FORMAT(MONTH(MAX('Dynamic Calendar'[Date])),"0#")) , VALUE(YEAR([Employment end date]) & FORMAT(MONTH([Employment end date]),"0#"))))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Persion ID",[Person ID],
    "Year",[Year],
    "Month",[Month]
    
)
RETURN  COUNTAX(tmpTable,[Persion ID])

    If you still have problem, please share your expected output with little sample data.

     

    Best Regards,

    Lin

    • SofG's avatar
      SofG
      Frequent Visitor
      6 years ago

      Thank you! When trying this formula it says "Cannot convert value '11/30/202511' of type text to type date. Do you know how to change to type date?

       

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi, SofG 

        '11/30/202511' is not the format of date, and I don't find it in the sample pbix file.

        what is "11" in this string 11/30/202511'?

         

        Best Regards,

        Lin