Forum Discussion
Revenue Recognition Calculation Issue
It i not clear to me what your expected outcome is other than the table above (which matches my proposal exactly)
Hi,
I think the issue is here.. You have stored the Percentages in a table and then use a measure Perc to sum this up. The problem is that the Percentage is a measure in my situation =cost of the month divided by the sum of that month and future forecasted costs. This is dynamic as the forecasts could change and so would this percentage. I have a measure defined for the above and this is working fine. Now this % has to be multiplied by 139000 (less sum of revenue calculated for earlier months). Since the Cost percent is a measure and not stored in a table, i was wondering how to sum this as you have done using your perc measure. That i think is one of the issues
For Oct the calculation is as such: Cost of October (Since it is a past month, this cost is actual) divided by the total cost (Total Cost measure takes the actual cost of any month and adds this to the forecasted cost of all future months). Formula is Cost for the month divided by total costYour sample data does not include a cost column.
The sample data has a cost percentage which is a measure and calculates the percentage as below.
What is do is the following:
One measure called Actual Cost: This measure looks at the time entered by each employee in the time entry table and then looks at the blended cost rate of each employee and calculates the actual cost for the month.
One measure called Forecasted Cost: This looks at the forecast table (Where time is forecasted by Employee per period). Sums the time after the max date of actual time (That way it only looks at cost for the future) and then looks at the blended cost rate of each employee and calculates the forecasted cost for the remaining portion of the project duration.
One measure called Total Cost: This adds the above two measures Actual cost for the period and the forecasted cost for the future periods to get the total remaining cost as on the period (Does not look at past cost)
Then finally i derived the Cost Percent Measure where the Actual cost for the period is divided by the Total Cost. This percentage is what i have shown in the sample data in the Cost Percent Column.The above measures are working fine.
Why are these measures and i didnt try calculated columns? Reason is forecast data changes every month and hence the %'s for cost in the future periods also keep changing. What has happened in the past is based on actuals and these dont change. That is why forecasted cost is calculated only after the Max of the time entry date in the time entry table.
I hope someone else can help you further.