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 hoping to show a cumulatively increasing line on a graph with the projections for the whole project on one axis and [actual cumulative sales] (I already have that field) on a graph. Is this possible? I've managed to make my axis start at [start date] and end at [end date], it's just the daily bit I'm struggling with.

 

Thanks!

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • 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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi NMC20 ,

     

    Usually, we often create the following measure to return the cumulative sum:

    actual cumulative sales =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Date] <= MAX ( 'Table'[Date] ) )
)

     Here's the example based on some dummy data:

    You can download my attachment for more details.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.