Forum Discussion

RajeshPBI's avatar
RajeshPBI
Frequent Visitor
2 years ago

Revenue Recognition Calculation Issue

 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...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

Here's a measure 

 

 

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

 

RajeshPBI's avatar
RajeshPBI
Frequent Visitor
2 years ago

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

    • RajeshPBI's avatar
      RajeshPBI
      Frequent Visitor
      2 years ago

      Hi Ibendin,

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

      Project CodeP 1000 
      Total Expected Revenue (Over Life Of The Project)1,39,000 
      Period% Revenue Recognized
      Oct-237.00%$9,730.00
      Nov-2312.00%$15,512.40
      Dec-2318.00%$20,476.37
      Jan-2424.00%$22,387.50
      Feb-2432.00%$22,686.00
      Mar-2451.00%$24,585.95
      Apr-2447.00%$11,102.24
      May-2438.00%$4,757.43
      Jun-2460.00%$4,657.27
      Jul-24100.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's avatar
        lbendlin
        Super User
        2 years ago

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