Calculating monthly revenue from a start and end date range

[Forcemark]

Hi,

Currently I am tracking revenue from contracts in excel. I have a start and end date and then use the formula below to get the monthly revenue from the contracts in order to be able to forecast our total monthly revenue.

=(IF(F1>=B2,IF(C2="",$D2,IF(F1<=C2,D2,0))))

However I would like to be able to step away from excel and get the monthly revenue directly from connecting the datbase to Power BI. Would this be possible? 

Some assumptions:

If a start date or end date is in the middle of the month the revenue will last the whole month, there are no calculations needed based on number of days, therefore in Power BI I have adjusted all dates to be "end of month". 

Thanks,

[v-chuncz-msft]

@Forcemark,

One way is to add a calculated table.

https://community.powerbi.com/t5/Developer/Dax-Help-Create-table-with-variable-of-lines-according-to...

[Forcemark]

Thank you v-chuncz-msft, I'll have a look at that. Is it possible to get Calenderauto to create a table by year and months instead of days?

I've so far calculated the contracts which will I need for another excercise, I based it on the link below but changed the formula to count contracts without an end date.   

           ( Table1[Start Date] <= currentDate
                && ((Table1[End Date] >= currentDate
                || (Table1[End Date] = BLANK() )

https://community.powerbi.com/t5/Desktop/Calculating-a-monthly-employee-count-from-a-start-and-end-d...