Cumulative total / forecast for Revenue Forecast Techniques for Project Data

Jeremysmith · September 10, 2020, 3:05am

Hi All,

Following the guidance located in the revenue forecast technique here:

[Enterprise DNA Revenue Forecasting] (https://www.youtube.com/watch?v=t2JTOE76q0E&feature=youtu.be)

I’ve successfully created the measure for our project data which includes:

Day Rate ($)
Start Date
End Date

However, I’ve been unable to create a cumulative total using the cumulative total pattern, which has worked for other entities as the values are lost when we change the time. I’ve seen a few other people ask the same question but haven’t been able to find a solution that works.

I am looking to calculate:

Revenue per month (sum of all Day Rates for projects that are active for a given day for that month)
Cumulative Total (YTD)

Revenue Forecast Techniques For Project Data - Power BI Insights with DAX.zip (169.4 KB)

Thanks for your help!

Harsh · September 10, 2020, 7:43am

Hello @Jeremysmith,

Thank You for posting your query onto the Forum.

Well, I’m not sure about the first point that you’ve asked still unclear what’s required there. But for the second point I’ve calculated the cumulative totals in both the ways. i.e. cumulative total over a period of time as well as cumulative total YTD. Below the formulas are provided below for the reference alongwith the screenshot of the final results -

Cumulative Revenue 1 = 
CALCULATE(
    SUMX( SUMMARIZE( Dates , Dates[Date] , "Cumulative Revenue" , [Estimate Revenue] ) , [Cumulative Revenue] ) , 
        FILTER( ALLSELECTED( Dates ) , 
            Dates[Date] <= MAX( Dates[Date] ) ) )

Cumulative Revenue 2 = 
CALCULATE(
    SUMX( SUMMARIZE( Dates , Dates[Date] , "Cumulative Revenue" , [Estimate Revenue] ) , [Cumulative Revenue] ) , 
        DATESYTD( Dates[Date] ) )