Values over time

[sgruber]

For a the above sales pipeline, I am trying to take the value of a lead and create a forecast of the revenue over time, based on it's value, start date and duration. I have done this in Excel and am trying to recreate in Power BI using a measure that returns the monthly recurring revenue for each month in a dimension table (the dim table is a list of of the first dates for all months).

Revenue = IF(AND(
                STARTOFMONTH('Leads'[Start Month]) >= STARTOFMONTH('Months'[Date]),
                STARTOFMONTH('Leads'[Start Month]) < DATEADD(STARTOFMONTH('Months'[Date]),SUM(Leads[Duration (Months)]),MONTH)),
            SUM(Leads[Monthly Revenue]),
            0)

When I create a pivot table of the measure, leads and dates, it shows all zero's, so can't get to show the monthly revenue per lead for each month.

Links to the the pbix file and excel file below:

PBIX file

Excel file

Am I going about this the right way? Some post's I've seen point to using a crossjoin rather, but I'm not sure how one would go about using a crossjoin in a measure? Any help would be greatly appreciated.

[v-cherch-msft]

Hi @sgruber

You may have a look at the sample file here to check if the measure matches your requirement.

Regards,

Cherie