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

Coops — Sat, 25 Mar 2023 18:26:56 GMT

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

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

Greg_Deckler — Sat, 25 Mar 2023 19:11:15 GMT

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

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

Coops — Mon, 27 Mar 2023 18:33:03 GMT

Greg_Deckler Thank you so much, this works perfectly!

topic Re: Help creating Cumulative Revenue from Annualised Revenue Estimates and go live date in DAX Commands and Tips

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

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

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