Recurring revenue and one time revenue in stack column visual

[Anonymous]

Trying to visualize the following on the same stack column visual
1. Recurring revenue. For example, $1000 January through December
2. One time revenue. If there was a new deal in February, I want to divide the new amount by the number of months left until the end of the year, and add this amount to the already existing revenue from #1 above.
Example, a new deal of $550 was signed in February. 550/11=55
Need the January column to show $1000, and from February to December columns should now show $1055.

Need an advise on how to create such measure, that will first apply the baseline revenue to all months, and dynamically add the one time revenues on top of the baseline.

Many thanks!

[v-yulgu-msft]

Hi @Anonymous,

Please refer to this blog to provide more necessary details.

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft, 

Indeed a clearer explanation is needed here. 

Please see the table and visual below as I created in Excel with sample data. 

1. When you have a new booking, the Total amount is divided by the months left from the new booking month, to the end of the year, (If the booking was on February, Booking RMR =New Booking/11. Same goes for cancellations. 

2. Total Monthly Revenue=Previous month revenue+Booking RMR current month+Cancellation RMR current month

3. The bar should then display RMR, New Booking RMR, Cancellation RMR, and Total Revenue each month.

In Excel, once you have a new booking or cancellation, you can easily calculate the impact and "drag" the result to all future months.

Question is how to apply that in Power BI?

You can download the Excel file here

Many Thanks!

Asaf