5 Year Headcount/FTE trend

[jkevin]

Hi,

We’ve only just started using Power BI at work and we’re currently re-creating reports/dashboards that had originally been created on OBIEE.

I’m working on HR data at present and what I’m trying to do is create a five year headcount and FTE trend in a clustered column visual.

I’ve got a report for current headcount and FTE i.e. today but what I’d like to do is have the headcount/FTE today, on this day last year, and on this day 2,3,4 and 5 years ago.

I’ve simplified the data table below

UniqueID	Valid_From	Valid_To	DAY_FTE	Headcount
12345	01 January 2016	31 December 2018	1	1
23456	01 August 2018	31 July 2020	1	1
34567	01 March 2019	31 March 2099	1	1

DAY_FTE is a calculation of FTE based on the contracted hours. Headcount is also a calculated field – a distinct count of Unique ID – which should always be one (Not sure why it was written like this as I’ve taken over someone’s unfinished work).

The existing FTE/Headcount report has been generated by summing up the headcount/DAY_FTE but having a page filter on the Valid_From and Valid_To dates using relative date filtering as below

I realise I’ll probably need to remove that as a page filter to create this trend but I’m slightly lost on the best way to proceed.

Can anybody point me in the right direction to create the visual I need?

Thanks

Kevin

(I won’t see any replies until Monday as I don’t work weekends. So please forgive me if I don’t reply instantly to any questions you may have)

[v-frfei-msft · Accepted solution]

Hi @jkevin ,

We can create a measure as below to get the headcount/FTE on this day last 5 years ago.

last 5 yeasr = 
VAR l5y =
    EDATE ( TODAY (), -60 )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[UniqueID] ),
        FILTER ( 'Table', 'Table'[Valid_From] <= l5y && 'Table'[Valid_To] >= l5y )
    )

BTY, Please find the pbix as attached. If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[jkevin]

Thank you all for the suggestions.

I've only just returned to work after being away for a couple of days. I'll make some time to look at your suggestions in the next few days.

Thanks once more.

Kevin

[lc_finance]

Hi @jkevin ,

I would be happy to help you.

I just wrote 2 blog posts on headcounts:

- a post on calculating HC (with dynamic dates) https://finance-bi.com/power-bi-employee-count-by-month/

- a post on calculating employee turnover rate https://finance-bi.com/power-bi-employee-turnover-rate/

Does this help you as to how to remove your page filter?

If it doesn't, could you share a sample Power BI file with mock data and a table with the numbers you expect to have (based on the same data)? 

You can share a Power BI file using One Drive, Google Drive or a similar tool and including a link in your reply.

Let me know. Regards,

LC

Interested in Power BI and DAX? Check out my blog at www.finance-bi.com

[jkevin]

Hi LC,

Now that I've looked at this again it does appear that what I selected as the 'answer' is only a partial answer. The problem being that it makes me create individual measures for each of the 5 previous years which then limits me to what visual I can use.

Having reread your blog I think that is the way to go but I'm struggling with one thing.

In your measure you create a selected date variable as follows 'selectedDate = MAX('Date'[Date])' However, my selected dates need to be Today, One Year ago Today, 2 years ago today etc. up to 5 years ago. And for the life of me I can't think of a way to do that in a single measure. Have you any suggestions? Is it even possible?

Thanks for any help you can offer.

Kind regards,

Kevin

[jkevin]

I actually managed to figure this out myself (obviously a few minutes after I asked for more help :-))

All I had to do was create a table of dates which held only the dates I needed to report on

So I used the following dax to create a table

All Years Dates = {(TODAY()), (EDATE ( TODAY (), -12 )), (EDATE ( TODAY (), -24 )), (EDATE ( TODAY (), -36 )), (EDATE ( TODAY (), -48 )), (EDATE ( TODAY (), -60 ))}

and then passed those dates to the selectedDates variable in your headcount formula.

I also adapted it to generate FTE count as well.

Thanks for all the help,

Kind Regards,

Kevin

[lc_finance]

Hi Kevin,

I am very glad to hear that you found a solution, and that you also managed to add an FTE.

Do not hesitate if you have any more questions,

LC