Forum Discussion

NMC20's avatar
NMC20
Helper I
1 year ago

Projected Revenue Based on Average Per Day

I have managed to get an [Average Sales Per Day] in my data set, along with [Days Until Project End] and, based on those two fields, [Total Projected Reveue] for the project.    However, I was hopi...
Need Help
NMC20's avatar
NMC20
Helper I
1 year ago

Hello,

 

Below is some sample data. [Days Since Install] and [Days Until Removal] are fields calculated based on today's date. 

 

I have a measure which works out a [Cumulative Revenue To Date]. 

I have a measure which works out [Average Revenue per Day] ([Total Revenue] / [Days Since Install])

I have a measure which works out [Projected Revenue] ([Average Revenue per Day] * [Total Days of Install])

 

On a chart, I have managed to show [Cumulative Revenue To Date] so an incrementally increasing line chart (see below).

With the above measures, I can see a final [Projected Revenue] for the duration of an installation of a project. However, what I would like to do (if possible) is see this broken down by day and show it on the [Cumulative Revenue To Date] chart.

 

I assume I need to divide [Projected Revenue] by [Days Since Install] then produce a cumulative measure for this. I'm just not sure if that will work on the chart below. 

 

I've done a very crude drawing of what I would like to do below. I can do the orange bit - it's the blue I'm struggling with.

 

 

I have managed to get the [Installation Date] as my minimum on the X axis and [Removal Date] as my maximum but I am struggling in working out whether I can see [Projected Revenue] by day, beyond the actual [Cumulative Revenue To Date]. 

 

I hope this helps explain what I'm after. I really appreciate the help!

 

Sample Data

 

Date of OrderLocationAmount PaidNet RevenueTransaction FeesInstallation DateRemoval DateDays Since InstallDays Until RemovalTotal Days of Install
01/12/2024Project 132.760.2401/12/202420/12/202414620
01/12/2024Project 121.770.2301/12/202420/12/202414620
02/12/2024Project 132.750.2501/12/202420/12/202414620
02/12/2024Project 121.770.2301/12/202420/12/202414620
03/12/2024Project 132.750.2501/12/202420/12/202414620
03/12/2024Project 132.760.2401/12/202420/12/202414620
04/12/2024Project 122.760.2401/12/202420/12/202414620
04/12/2024Project 132.750.2501/12/202420/12/202414620
05/12/2024Project 121.760.2401/12/202420/12/202414620
05/12/2024Project 132.770.2301/12/202420/12/202414620
06/12/2024Project 121.750.2501/12/202420/12/202414620
06/12/2024Project 232.770.2306/12/202402/02/202594453
07/12/2024Project 232.750.2506/12/202402/02/202594453
07/12/2024Project 121.760.2401/12/202420/12/202414620
08/12/2024Project 232.760.2406/12/202402/02/202594453
08/12/2024Project 221.750.2506/12/202402/02/202594453
09/12/2024Project 132.760.2401/12/202420/12/202414620
09/12/2024Project 232.770.2306/12/202402/02/202594453
10/12/2024Project 132.750.2501/12/202420/12/202414620
10/12/2024Project 132.770.2301/12/202420/12/202414620
11/12/2024Project 221.750.2506/12/202402/02/202594453
12/12/2024Project 132.760.2401/12/202420/12/202414620
12/12/2024Project 232.760.2406/12/202402/02/202594453
13/12/2024Project 232.750.2506/12/202402/02/202594453
14/12/2024Project 232.750.2506/12/202402/02/202594453
lbendlin's avatar
lbendlin
Super User
1 year ago

Needs a disconnected calendar table, and you will need to consider how to display the individual projects.

 

Note:  This should normally be done via LINESTX.

 

projected revenue.pbix30 KB
  • NMC20's avatar
    NMC20
    Helper I
    1 year ago

    Thank you for this! I will give this a try next week. 

  • NMC20's avatar
    NMC20
    Helper I
    1 year ago

    I've only just had chance to properly look at what you have created and it exactly what I'm after! Thank you so much for taking the time to help me. 

    I have a single selection filter to show each project one by one (I never need to see them all together). 

     

    I've tried to recreate your work but the Actuals RT and Forecast RT measures are coming back blank.

    All I've done is create the disconnected Calendar table with the same DAX as you then created Actuals RT and Forecast RT with the same DAX as you. There are no errors showing, they are just blank. Have I missed something?

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      You can use EVALUATEANDLOG to figure out where your results differ from mine. Or provide a different set of sample data

       

      • NMC20's avatar
        NMC20
        Helper I
        1 year ago

        Hi again, 

         

        I've tried to understand why I can't get my data to match yours but I'm really struggling so I've anonymised my actual report and attached here Power BI Help

         

        I would also like a "cost" line which is permanently across the chart (as it looks now) but that increases each month as the monthly costs are added on to the upfront cost. Basically, I would like this chart to show me when the projected is due to hit the cost line and then how much it increases after that. 

         

        Thanks so much for the help so far - I'm doing this in addition to my day job so it's slow going!