Forum Discussion
Manning Forecast
Hello all!
I'm looking to create a forecast of the manning level in my organisation over a couple of years, taking into account fixed term contracts. I have their names, join date and projected contract end date in a table that looks like this:
If anyone could help me out that would be great - new to Power BI this week and want to demonstrate how it can be of use to the company!
Many thanks
Hi BenEaton,
If I understood you correctly, you want to calculate the number of stilled working employees, right?
You can create a calendar table:
Calendar = CALENDAR(MIN('Table1'[Join]),MAX('Table1'[End]))
Then create a measure in Table1:
Still Employed = COUNTAX(FILTER(ALL(Table1),[End]>=MAX([End])),[Name])
Create a new table:
Table = DISTINCT(SELECTCOLUMNS('Calendar',"Date",[Date],"Still Employeed",COUNTAX(FILTER(ALL(Table1),[End]>=EARLIER('Calendar'[Date])),[Name])))
Add a forecast line to the line chart.
Best Regards,
Qiuyun YuI might have figured it out as COUNTAX(FILTER(Plot,[Join]<=EARLIER(Calendar[Date]) && [End]>=EARLIER(Calendar[Date]))), it appears to show what I need it to! I am still a bit confused about the use of EARLIER, but it does what I need it to and some more playing with the expression might be more revealing.
Many thanks v-qiuyu-msft for a bit of guided learning! If there's a more logical way of writing what I have written please let me know! If only now I can add a slicer I'm fully set!
Ben
3 Replies
Hi BenEaton,
If I understood you correctly, you want to calculate the number of stilled working employees, right?
You can create a calendar table:
Calendar = CALENDAR(MIN('Table1'[Join]),MAX('Table1'[End]))
Then create a measure in Table1:
Still Employed = COUNTAX(FILTER(ALL(Table1),[End]>=MAX([End])),[Name])
Create a new table:
Table = DISTINCT(SELECTCOLUMNS('Calendar',"Date",[Date],"Still Employeed",COUNTAX(FILTER(ALL(Table1),[End]>=EARLIER('Calendar'[Date])),[Name])))
Add a forecast line to the line chart.
Best Regards,
Qiuyun YuHi v-qiuyu-msft,
Many thanks, that's brilliant. What I am trying to work out now is how to take into account the join date - as the line chart only takes into account end date and the starting line is simply the sum of all employees in the data, regardless of join date.
I think it's to do with COUNTAX(FILTER(ALL(Table1),[End]>=EARLIER('Calendar'[Date])) only referencing [end], not [Join]. At the moment trying to find a way to express almost a "between"!
I might have figured it out as COUNTAX(FILTER(Plot,[Join]<=EARLIER(Calendar[Date]) && [End]>=EARLIER(Calendar[Date]))), it appears to show what I need it to! I am still a bit confused about the use of EARLIER, but it does what I need it to and some more playing with the expression might be more revealing.
Many thanks v-qiuyu-msft for a bit of guided learning! If there's a more logical way of writing what I have written please let me know! If only now I can add a slicer I'm fully set!
Ben