How to calculate revenue for each month between two dates

[CarlosElias]

Hello everyone.

I have been traying to create a model where I can forcast the next months or even years revenue, acording to the amount pay by each client in a monthly basis.

The data set that I have looks something like this, where each costumer have a promotional price for a number of months and after that he start paying a higher price until forever (3022) or in some cases for a year. Also a costumer may have from 1 to many products (Q), so the amount charge every month will be Q * Invoice, for each month bewteen the start and end date.

I wanna be able to visualize this behavior in a table like this, because the data set is around 500.000 rows long.

If someone can help me I'll be so much grateful. I have been hitting my head to the wall for a few days with this.

Thanks in advanced.

ps: I already have a calendar table conected to the model.

[v-lili6-msft · Accepted solution]

hi  @CarlosElias 

here is a similar post for you refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Regards,

Lin

[CarlosElias]

This is exactly what I needed, such elegant solution that I've could never write it myself. Thanks A LOT!

[Ashish_Mathur]

Hi,

See if this technique helps - https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99.

[CarlosElias]

Hi, thanks for your response.

I was able to perform the solution at least in power query, but when I press "Save and apply" the following message appears:

OLE DB or ODBC error: [Expression.Error] The argument 'increment' is out of range.

Any idea what this means?

Thanks again.