Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello,
I have Position Table where I have main fields Position Valid from, Position Valid To and FTE
I want to calculate FTE for Last 12 month based on the Start date of Month.
For eg. Last 12 month
1 July 2018 ===> This date should be greater than Position Valid From and This date should be less than Position Valid To
1 Aug 2018 ===> This date should be greater than Position Valid From and This date should be less than Position Valid To
1 Sept 2018 ===> This date should be greater than Position Valid From and This date should be less than Position Valid To
until
1 July 2019 ===> This date should be greater than Position Valid From and This date should be less than Position Valid To
What should be the best approach to do this calculation?
Regards,
Parag
Hi.
Completely unclear what it is you want. Please restate the problem in an understandable way. Then maybe I'll be able to help you. Some pictures would be helpful. Also, the explanation with "===>" is totally unclear to someone that has not seen what you've been working on. On top of that... what's FTE?
Thanks.
Best
Darek
Hi Darek,
I am working on Human resource module in ERP and I have Employees with Positions.
I have Position table where I have Position Id, Position Valid From, Position Valid To and FTE(Full time efficiency)
I want to calculate FTE for Last 12 month based on Active Position and display data in Power BI.
Position id | Position Valid From | Position Valid To | FTE |
1 | 01/Jan/19 | 31/Dec/19 | 0,5 |
2 | 01/Feb/19 | 31/Dec/19 | 0,2 |
3 | 01/Mar/19 | 31/Dec/19 | 0,1 |
4 | 01/Apr/19 | 31/Dec/19 | 1 |
5 | 01/May/19 | 31/Dec/19 | 1,2 |
6 | 01/Jun/19 | 31/Dec/19 | 1,5 |
Months | Total FTE |
01/Jul/18 | |
01/Aug/18 | |
01/Sep/18 | |
01/Oct/18 | |
01/Nov/18 | |
01/Dec/18 | |
01/Jan/19 | |
01/Feb/19 | |
01/Mar/19 | |
01/Apr/19 | |
01/May/19 | |
01/Jun/19 | |
01/Jul/19 |
Cal;culation for Active Position is
For example
FirstDateOfMonth = 1 Jan 2019
SumOfFTE =
CALCULATE(SUM(Positiont[FTE]),
FILTER(Positiont, Positiont[Position Valid From] <= [FirstDateOfMonth ] && Positiont[Position Valid To] >= [FirstDateOfMonth ] ))
What is the best approach for this requirement?
Regards,
Parag
OK, this is still not fully clear... Would you mind sharing a link to a .pbix file with the model and some more data than you've posted here?
You say: "I want to calculate FTE for Last 12 month based on Active Position..." First, I understand that "FTE for last 12 month" means:
I want to sum up the FTE's for all the Position id's where Position Valid From is <= currently selected month from an independent table that holds months and Position Valid From does not fall out of the 12 month period [currently selected month - 11, currently selected month].
That's rather easy to implement. However, what happens if there are not enough months to go back? Can Position Id be duplicated? Can Position Valid From have dups? What about Position Valid To? Can it be something different than 31/Dec/2019?
Please send me a link to a pbix file that has all the variety of data that can happen.
Thanks.
Best
Darek
Hi,
Please find PBIX file here.
https://github.com/paragchapre/PowerBIreport/blob/master/Position%20Test%20report%20with%20FTE.pbix
Regards,
Parag
User | Count |
---|---|
16 | |
14 | |
13 | |
12 | |
11 |
User | Count |
---|---|
19 | |
16 | |
15 | |
11 | |
9 |