Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
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.
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.
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 Order | Location | Amount Paid | Net Revenue | Transaction Fees | Installation Date | Removal Date | Days Since Install | Days Until Removal | Total Days of Install |
01/12/2024 | Project 1 | 3 | 2.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
01/12/2024 | Project 1 | 2 | 1.77 | 0.23 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
02/12/2024 | Project 1 | 3 | 2.75 | 0.25 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
02/12/2024 | Project 1 | 2 | 1.77 | 0.23 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
03/12/2024 | Project 1 | 3 | 2.75 | 0.25 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
03/12/2024 | Project 1 | 3 | 2.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
04/12/2024 | Project 1 | 2 | 2.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
04/12/2024 | Project 1 | 3 | 2.75 | 0.25 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
05/12/2024 | Project 1 | 2 | 1.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
05/12/2024 | Project 1 | 3 | 2.77 | 0.23 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
06/12/2024 | Project 1 | 2 | 1.75 | 0.25 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
06/12/2024 | Project 2 | 3 | 2.77 | 0.23 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
07/12/2024 | Project 2 | 3 | 2.75 | 0.25 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
07/12/2024 | Project 1 | 2 | 1.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
08/12/2024 | Project 2 | 3 | 2.76 | 0.24 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
08/12/2024 | Project 2 | 2 | 1.75 | 0.25 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
09/12/2024 | Project 1 | 3 | 2.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
09/12/2024 | Project 2 | 3 | 2.77 | 0.23 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
10/12/2024 | Project 1 | 3 | 2.75 | 0.25 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
10/12/2024 | Project 1 | 3 | 2.77 | 0.23 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
11/12/2024 | Project 2 | 2 | 1.75 | 0.25 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
12/12/2024 | Project 1 | 3 | 2.76 | 0.24 | 01/12/2024 | 20/12/2024 | 14 | 6 | 20 |
12/12/2024 | Project 2 | 3 | 2.76 | 0.24 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
13/12/2024 | Project 2 | 3 | 2.75 | 0.25 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
14/12/2024 | Project 2 | 3 | 2.75 | 0.25 | 06/12/2024 | 02/02/2025 | 9 | 44 | 53 |
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.
User | Count |
---|---|
20 | |
10 | |
10 | |
9 | |
7 |