Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Calculate data for Last 12 month based on another table

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

4 REPLIES 4
Anonymous
Not applicable

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

Anonymous
Not applicable

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 idPosition Valid FromPosition  Valid ToFTE
101/Jan/1931/Dec/190,5
201/Feb/1931/Dec/190,2
301/Mar/1931/Dec/190,1
401/Apr/1931/Dec/191
501/May/1931/Dec/191,2
601/Jun/1931/Dec/191,5

MonthsTotal 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

Anonymous
Not applicable

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

Anonymous
Not applicable

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors