Forum Discussion
Revenue Recognition Calculation Issue
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.
Hi,
Made Some progress but got stuck in the below point.
Original Fixed Fee Amount was $139,000. First year revenue recognized was $11,344 and the Net Fixed Fee Amount available for further allocation of revenue was $139,000 less $11,344 which is $127,656.
In DAX – my understanging is that VAR is a constant and we cannot change the value of the variable inside the code. Hence I stored the above $127,656 in a variable BUDGETAMOUNTLESSYEAR1 and that is showing properly in the output.
I wanted to use this as an input for calculation of revenue for the second period and I am calling BUDGETAMOUNTLESSYEAR1 in another variable inside an IF clause which checks for Period 2. This variable is called PeriodTwoBudget. Ideally it should be the same $127,656. However, it is showing $121,814.
Don’t understand how this number changed from $127,656 which was the value in the original variable to $121,814. No other filters in the visual other than project number.
Actual Cost % To Forecast is a measure as i had explained earlier, to calculate the cost % and that is working as expected.
Can you look at this and maybe identify what is affecting the number?
RR Period Measures =
VAR StartDate=FORMAT(MIN('Project Master'[Start Date]),"MMM YYYY") -- Jun 2023
VAR SelectedPeriod=MIN('MS Calendar'[Month Year]) -- Respective Periods
VAR BUDGETAMOUNT = sum('Project Master'[Fixed Fee/Cap]) -- $139,000
VAR PeriodOne = [Actual Cost % To Forecast]*BUDGETAMOUNT -- $11,344 (8.16% * 139000)
VAR BUDGETAMOUNTLESSYEAR1=(BUDGETAMOUNT-PeriodOne) -- $127,656 ($139,000 - $11,344)
Return
IF(SelectedPeriod=StartDate,BUDGETAMOUNTLESSYEAR1,
VAR PeriodTwo=FORMAT(EDATE(DATEVALUE("1 " & StartDate),1),"MMM YYYY")
VAR PeriodTwoBudget=BUDGETAMOUNTLESSYEAR1 -- $121,814 (this is where the problem is. Should be $127,656)
VAR PeriodTwoRR = [Actual Cost % To Forecast]*PeriodTwoBudget -- 12.36% * $121,814
return
IF(SelectedPeriod=PeriodTwo,PeriodTwoBudget))