Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
NMC20
Frequent Visitor

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!

8 REPLIES 8
v-stephen-msft
Community Support
Community Support

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:

vstephenmsft_1-1735788007194.png

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.

 

lbendlin
Super User
Super User

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

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.

 

Chart.png

 

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

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

 

lbendlin_0-1735834511521.png

Note:  This should normally be done via LINESTX.

 

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?

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

 

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!

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors