Calculating Cumulative Monthly pipeline Totals

[Anonymous]

I am trying to create a matrix showing cumulative pipeline est. revenues by Month and deal stagefor a year. I have a monthly projected revenue amount per deal and est. close date (with revenues coming in the month after the est. closing date). So if I have several deals in pipeline closing in March, May, and August, summing up to $100 monthly revenue per stage per month, the table would show $0 for January-March, $100 for April and May, $200 in June-August and $300 for Sep-Dec.

I tried to use running total quick measure but it does not add up the previous month revenue to the current month.

I would appreciate any advice!

Olga

[v-yulgu-msft]

Hi @Anonymous ,

Above screenshot shows your desired output, right? Then how is the source data table?

Regards,

Yuliana Gu

[Anonymous]

yes, that was an example how the output should look like. Attached is the source table. The weighted amount is a calculated column (amount * probability). Monthly Weighted Revenue is also calculated as Weighted Amount/Contract term.

I need to calculate the projection of monthly revenue for the year by Deal Stage. The Monthly weighted revenue for a particular deal should appear in a month following after Estimated Close date and in every month until the end of the year. The output table would dispay a sum of projected monthly revenue for all deals in a deal stage by Month.

Would appreciate any advice!

Deal ID	Deal Stage	Estimated Close Date	Amount	Contract term	Probability	Weighted Amount	Monthly Weighted Revenue
485497494	Pitched	Sunday, December 1, 2019	18000	24	0.4	7200	600
611910473	Pitched	Sunday, March 31, 2019	18000	24	0.4	7200	600
485477092	Pitched	Monday, April 1, 2019	18000	24	0.4	7200	600
485486995	Engaged	Wednesday, May 1, 2019	12000	24	0.6	7200	600
485473861	Pitched		18000	24	0.4	7200	600
485507747	Pitched	Monday, April 1, 2019	18000	24	0.4	7200	600
485497483	Pitched	Tuesday, October 1, 2019	30000	24	0.4	12000	1000
485473843	Pitched	Wednesday, July 1, 2020	30000	24	0.4	12000	1000
485473847	Pitched	Sunday, September 1, 2019	30000	24	0.4	12000	1000
485477106	Pitched	Friday, November 1, 2019	30000	24	0.4	12000	1000
485487005	Pitched	Wednesday, January 1, 2020	30000	24	0.4	12000	1000
485512835	Pitched	Sunday, March 1, 2020	30000	24	0.4	12000	1000
485497481	Pitched	Sunday, December 1, 2019	30000	24	0.4	12000	1000
485483408	Engaged	Saturday, June 1, 2019	21000	24	0.6	12600	1050
485512834	Pitched	Friday, November 1, 2019	36000	24	0.4	14400	1200
485516774	Pitched	Sunday, September 1, 2019	36000	24	0.4	14400	1200
485497489	Pitched	Sunday, September 1, 2019	36000	24	0.4	14400	1200
485507752	Pitched	Saturday, June 1, 2019	36000	24	0.4	14400	1200
485505114	Pitched	Friday, November 1, 2019	36000	24	0.4	14400	1200
485490881	Pitched	Tuesday, October 1, 2019	36000	24	0.4	14400	1200

Thank you!

[JayHerrera]

@Anonymous   You can try using DAX measure as below to sum cumulative value. 

CALCULATE(SUM(<dataset>[<revenue field>])
,FILTER(ALLSELECTED(<dataset>)
,MAX(<dataset>[<date period>]) >= <dataset>[<date period>]))