Forum Discussion

sgruber's avatar
sgruber
Frequent Visitor
7 years ago

Values over time

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.

Need Help

1 Reply