Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |