Average monthly revenue between 2 dates

[PaulPBI88]

Hi All, 

I hava a question regarding how to calculate the average revenue per month between 2 dates of 1 contract and link this to the period. 

My data model look something like the following: 

Contract_ID              Start_date          End_date             Total_revenue

XWZ                         01.01.2018         31.12.2018           12000 USD 

What I want to get is the average monthly fee of the contract (so 1k USD per month in the example), and show this is a trend line based on the period (preferably using DAX). So after I have the average fee per month i will still need to link it to the contract, which is giving me a headache. 

Thank you in advance! 

Regards, Paul 

[v-danhe-msft · Accepted solution]

Hi @PaulPBI88,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem in below picture.

2.Create a new calculated column to calculate your average fee per month.

Average Revenue = DIVIDE([Total_revenue],DATEDIFF(Sheet1[Start_date],Sheet1[End_date],MONTH)+1)

3.Create a Line chart visual and add the [Contract_ID] and the [Average Revenue] field and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/66byr51q1n4dnmk/Average%20monthly%20revenue%20between%202%20dates.pbix?dl=...

Regards,

Daniel He

[PaulPBI88]

Hi @v-danhe-msft, 

This is brilliant! Thank you so much for your support! 

Regards,  Paul