Headcount growth rate

[mariomyhanh]

Good afternoon, 

I'm trying to figure out a dax to calculate the growth % year over year for each dept  with the sample query below.

I created a clustered column chart to show # of distinct position each year by department using a  query with similar headings below.

I would like to create a formula to show growth % year over year within each dept when hovering over certain year (sample graph i would like to show below).  Please help.  Thank you

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Position # count: = 
COUNTROWS( VALUES(Data[Position #]) )

Position # count YoY: =
VAR _currentyear = [Position # count:]
VAR _prevyear =
    CALCULATE ( [Position # count:], FY[FY] = MAX ( FY[FY] ) - 1 )
RETURN
    DIVIDE ( _currentyear - _prevyear, _prevyear )

[mariomyhanh]

Is there a way to count position # using distinct count? for example, if there's a repeated position number in the same fiscal year to only count it once. I tried this forumula

DistinctCountofPosition = DISTINCTCOUNT('Staffing Roster'[Position])

and it works in a table, but when i try to add it to the dax provided for YoY% it will not work.

Position # count YoY: =

VAR _currentyear = [DistinctCountofPosition]

VAR _prevyear =

    CALCULATE ( [DistinctCountofPosition], FY[FY]= MAX ( FY[FY] - 1 )

RETURN

    DIVIDE ( _currentyear - _prevyear, _prevyear )

please help

[Jihwan_Kim]

Hi,

thank you for your message.

I am not sure but please check if you missed to insert  ")" like the below.

Position # count YoY: =
VAR _currentyear = [DistinctCountofPosition]
VAR _prevyear =
    CALCULATE ( [DistinctCountofPosition], FY[FY] = MAX ( FY[FY] ) - 1 )
RETURN
    DIVIDE ( _currentyear - _prevyear, _prevyear )

Or, please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[mariomyhanh]

Thank you so much for your help and patience.  here is the link to the sample data. I hope you are able to access it.  

Sample Headcount growth rate data.pbix

[Jihwan_Kim]

Hi,

Thank you for your link, but I cannot accesss to it.

[mariomyhanh]

Unfortunately, i am not able to share the file. So sorry. When adding the YoY to the value field table (see below) it returns 0 for all the  FY YoY.  . I hope this helps. Thank you so much again.

[Jihwan_Kim · Accepted solution]

Hi,

I think my sample model is simpler than yours.

In order to get previous year's numbers in your model, please try using relevant column from Dim Date table.

Thanks.