Cumulative total / forecast for Revenue Forecast Techniques for Project Data

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!

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] ) )

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Revenue Forecast Techniques For Project Data.pbix (171.6 KB)

1 Like

Thanks @Harsh! Have been trying to get that to work for days.

Much appreciated!

Hello @Jeremysmith,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Thanks and Warm Regards,
Harsh