cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How to calculate revenue for each month between two dates

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.

 

Captura.PNG

 
 

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

 

Captura1.PNG

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.

1 ACCEPTED SOLUTION
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Super User III
Super User III

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors