Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
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.
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |