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.
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
Solved! Go to Solution.
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 Yu
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
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 Yu
Hi @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
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |