Revenue Recognition Calculation Issue

[RajeshPBI]

 Hello,

Business Requirement for DAX: Calculation of Revenue for a period.
1 - Cost Percentage for a period is derived from a measure called CostPercent which is already defined and in use (Denoted by % in the excel). This is a % derived from the actual cost for each period and the sum of actual cost for that period along with the forecasted costs for the remaining periods. This is working as expected.
2- ExpectedRevenue is the column in the Project Master Table which has the Total expected revenue.
I need Dax Code for the Revenue Calculation, the logic is explained below.
- First month of the project - CostPercent Multiplied by TotalEstimatedRevenue
- Second Month Of the Project - CostPercent Multiplied by TotalEstimatedRevenue Less Revenue of the previous month
- Third Month - CostPercent Of each Month Multiplied By the TotalEstimatedRevenu Less Revenue of month one and two.

In Short: Revenue for a particular month is the multiplication of the % with the (Expected Revenue Less Revenue already recognized in previous periods).Please Note this revenue which is recognized is NOT being stored in any table. It is all calculated on the matrix Visual with the calendar months as the rows and measures in the columns as depicted in the attached excel...

I am able to calcualte the simple formula of cost % by Expected revenue. But i need to deduct the sum of the earlier months revenue from the expected revenue before i multiply this with the cost % dynamically on the visual and this is where i am stuck.

We have 100's of projects and project code is a slicer and when the chooses a project code, the visual will need to represent the right numbers.
Can i do this using just a measure is there an alternate soluion? What is the best approach for the above?

Project Code	P 1000
Total Expected Revenue (Over Life Of The Project)	1,39,000
Period	%	Revenue Recognized
Oct-23	7.00%	$9,730.00
Nov-23	12.00%	$15,512.40
Dec-23	18.00%	$20,476.37
Jan-24	24.00%	$22,387.50
Feb-24	32.00%	$22,686.00
Mar-24	51.00%	$24,585.95
Apr-24	47.00%	$11,102.24
May-24	38.00%	$4,757.43
Jun-24	60.00%	$4,657.27
Jul-24	100.00%	$3,104.85

[lbendlin]

Here's a measure 

I have cleaned up your sample data a little to make it usable.

[RajeshPBI]

Thank you So much Ibendlin. The one thing which is different is tha the Cost Percentage is a measure, not a value stored in the column of a table. The measure is working well when used separately. I want to use the same measure which calculates this % on the visual for each row to also be used in calculation of the revenue recognized every period. Is that possible?

Regards

[lbendlin]

Now implemented with measures.

[RajeshPBI]

Hi Ibendin,

Really thankful for your help but the numbers seem to be off. Based on the table below, let me try explaning.

Project Code	P 1000
Total Expected Revenue (Over Life Of The Project)	1,39,000
Period	%	Revenue Recognized
Oct-23	7.00%	$9,730.00
Nov-23	12.00%	$15,512.40
Dec-23	18.00%	$20,476.37
Jan-24	24.00%	$22,387.50
Feb-24	32.00%	$22,686.00
Mar-24	51.00%	$24,585.95
Apr-24	47.00%	$11,102.24
May-24	38.00%	$4,757.43
Jun-24	60.00%	$4,657.27
Jul-24	100.00%	$3,104.85

Let us say the project started in Oct 2023. This is the first month of the project. The project has a forecast from the beginning of the project until the end. We are in November.

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 cost

The above is a %

Month of October: Since this is the first month of the project, the revenue recognized is a simple multiplication of the Cost % by 139,000. as per the table above it is 7% and the revenue recognized should be $9,730

Month of November. The Cost Percent for this month is 12% (Cost OF November divided by total cost which is November plus forecasted cost until end of the project).

Revenue for November has to be 12% multiplied by 139000-9730 (9730 is the revenue recognized in Oct) and this amount is $15,512

Revenue for December: Let us say the cost percent is 18% (Cost of Dec divided by Total cost which is cost of december plus cost of all future months).

Revenue Recog for Dec is 18% multiplied by 139000 Less (9730 (Oct Rev) plus 15512 (Nov Rev)) which is $20476.

This will continue for all future months until the end of the project.

The total of all the Cost Percent Measure is never 100% as a sum

In short: We multiply the current months Cost Percent by the Net BudgetRevenue which is (Totalbudgetrevenue less revenue recognized until the current period).

Cost measure is working fine for me. I am unable to figure out how to derive the net budget revenue for the current period. The exact place i am stuck is this: Since the revenue is not captured in any table/column i was trying to figure out how to sum the revenue of the previous months via the measure

Hope i have better explained the requirement.

Regards

[lbendlin]

It i not clear to me what your expected outcome is other than the table above (which matches my proposal exactly)

[RajeshPBI]

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

[lbendlin]

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 cost

Your sample data does not include a cost column.

[RajeshPBI]

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.

[lbendlin]

I hope someone else can help you further.

[RajeshPBI]

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))

[Ahmedx]

hi lbendlin I have a question, why did you use the EVALUATEANDLOG function? if you remove it the result will not change

[lbendlin]

I used it for troubleshooting and forgot to remove it before posting.