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