Help creating Cumulative Revenue from Annualised Revenue Estimates and go live date

[Coops]

Hi, 

I am trying to create a report which shows a revenue forecast, based on the expected go live dates of a number of clients, and their expected annualised revenue.  

I have a table like the following 

Client	Target Go Live Date	Estimated Revenue Annualized
Client A	3/31/23	$ 100,000
Client B	5/26/23	$ 400,000
Client C	9/14/23	$ 600,000

I am trying to work out the total expected revenue for the year by 

- calculating the daily reveue for each date by  Summing the Estimated Revenue Annualized / 365 days for all clients where the Go Live Date < Date

- Calculating the cummulative daily revenue for each date. 

My goal is to end up with something similar to the below table - but have been struggling with getting the DAX to behave. 

Any help in how this is be done is greatly appreciated!  

Month	Client A Cumm. Rev.	Client B Cumm. Rev	Client C Cumm Rev.	Total Cumm Rev
Jan	$                            -	$                          -	$                           -	$                    -
Feb	$                            -	$                          -	$                           -	$                    -
Mar	$                            -	$                          -	$                           -	$                    -
Apr	$                 8,213.55	$                          -	$                           -	$          8,213.55
May	$               16,700.89	$               5,475.70	$                           -	$        22,176.59
Jun	$               24,914.44	$             38,329.91	$                           -	$        63,244.35
Jul	$               33,401.78	$             72,279.26	$                           -	$      105,681.04
Aug	$               41,889.12	$           106,228.61	$                           -	$      148,117.73
Sep	$               50,102.67	$           139,082.82	$              26,283.37	$      215,468.86
Oct	$               58,590.01	$           173,032.17	$              77,207.39	$      308,829.57
Nov	$               66,803.56	$           205,886.38	$            126,488.71	$      399,178.64
Dec	$               75,290.90	$           239,835.73	$            177,412.73	$      492,539.36
Grand Total	$               75,290.90	$           239,835.73	$            177,412.73	$      492,539.36

[Coops]

@Greg_Deckler  Thank you so much, this works perfectly! 

[Greg_Deckler · Accepted solution]

@Coops Try these two measures. Use Cumm Rev Total in your matrix. PBIX is attached below signature.

Cumm Rev = 
    VAR __StartDate = MIN('Table'[Target Go Live Date])
    VAR __Revenue = SUM('Table'[Estimated Revenue Annualized])
    VAR __PerDay = __Revenue / 365
    VAR __MaxDate = MAX('Dates'[Date])
    VAR __Diff = ( __MaxDate - __StartDate ) * 1.
    VAR __Result = IF( __Diff < 0, 0, __PerDay * __Diff)
RETURN
    __Result



Cumm Rev Total = 
    VAR __Table = SUMMARIZE('Table', [Client], "__Revenue", [Cumm Rev])
    VAR __Result = IF(HASONEVALUE('Table'[Client]), [Cumm Rev], SUMX(__Table, [__Revenue]))
RETURN
    __Result