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
Simple Cost Ratio from GL Data
Hello all, pretty frustrated with what I feel should be a simple task I have a simple set of GL data, as seen below, where my amount is in one column, and my accounts are rows. I then do a very simple model where I have a GL Account class lookup table, which assigns the class "revenue" and "cost" to each account. I then just want to have a simple cost ratio at the account level, of cost/total revenue. the table should look as such below, but nothing returns at the account level for a margin. even the revenue accounts are wrong, as Revenue Reg should be 60% of total revenue, , and Revenue Spec should be 40% My measures are Value = SUM(data_table[Amount]) Total Revenue = CALCULATE([Value],gl_class[Class]="Revenue") Cost Margin = DIVIDE([Value],[Total Revenue]) any help would be greatly appreciated
