Forcasting cost based on periodical costprice
Greetings, Been thinking about this problem for a few weeks now and can't seem to find the best solution, hopefully the community can help! We are trying to forcast the upcomming 'project'-year, this forcast is a combination of asked hours for each employee for each project times his costprice. But that's where this gets difficult. Because our costprice table had multiple prices per Employee: For example: EmployeeCostPrice EmployeeID EmployeeStart EmployeeEnd Employeeprice 1 1-1-2020 31-12-2020 60 1 1-1-2021 31-12-2021 70 1 1-1-2022 null 90 2 1-1-2021 31-12-2021 60 2 1-1-2022 31-12-2025 90 This table is an example table found in our systems. It shows how much a person costs in a specific period. Powerbi needs to know which row to pick depending on the period. For now we took the last row with a unique EmployeeID but this is also very inaccurate. ForcastNeededProjectHours ProjectID EmployeeID MonthlyPeriod AmountHours P1 1 1-1-2021 20 P1 1 1-2-2021 25 P1 1 1-1-2022 20 P2 1 1-1-2022 20 This is our table with the amount of hours a project asks an employee in an corresponding montly period. I think it should be possible with a relative measure but we don't know where to look next... Our question is: How can we make a visual with a relative measure (?) where the predicted total cost in a (montly) period for each employee for each project uses the right cost-price to calculate its total. Thanks in advance! FrenchFry
