Monthly trend line with average revenue between 2 dates

[PaulPBI88]

Hi all, 

I have the following challenge. I have a data set with contract information with a start and end date and the total revenue of the contract.

What I want to show now is a trend line of the monthly fee per contract (so x-axis average revenue p/m, and y-axis the year+month). I have already created some other metrics like average monthly fee, total number of months, etc (see example below).

The challenge I have is getting a visual that spreads the revenue of the contract over all the month in between (so for contract A in the attached example, Jan18 is 1k, Feb18 is 1k, and so on, which i can then show on a trend line).

 

Thank you for your support!

Regards, Paul 

[PaulPBI88 · Accepted solution]

Found the answer somewhere else: 

https://community.powerbi.com/t5/Desktop/Spread-revenue-across-contract-length/td-p/369777

[v-chuncz-msft]

@PaulPBI88,

You may drag date from a calendar table and apply virtual relationship in DAX to add an appropriate measure.

[PaulPBI88]

Hi Sam, 

Thank you for message! Unfortunately, that doesn't seem to work. I have tried to do this, but everytime i try it only takes the start date or end date as specific date values. So it doesn't spread it between the two dates. any idea how i could solve this? 

Thank you in advance! 

Regards, 

Paul