Hi guys,
I have been tasked with creating a report in Power BI for a project that we’re doing at workplace. So, what we’re doing at work is having a price increase for our products in August.
Now, using all products, I have created a ‘total revenue’ measure. This is just the revenue we are ‘expecting’ for August - the ‘best case scenario’ i.e. no attrition. (But I’ll get to attrition later).
Next, what I want to do is assuming there’s no attrition, I’ll have the same revenue for all the months till the end of the financial year. How do I do this? I can create a ‘date-table’ that goes up to June 2020. What measure can I create to populate the revenue for these months the same as total revenue for August?
Once that’s done, I also want to create a ‘cumulative total’ till June 2020.
Finally, I want to create a ‘what-if’ parameter for the initial attrition only in August. So, when I adjust the slider on the ‘attrition visual’, it will affect the cumulative revenue as well!
Many thanks for your help on this, guys!
I’ve got till here :
the measure ‘HC Revenue’ is just a simple measure I created that holds the value : 736581.
The cumulative revenue formula I used is this:
Cumulative Revenue = CALCULATE([HC Revenue], FILTER(ALL('Date Table'), 'Date Table'[Date] <= MAX('Date Table'[Date])))
And you can see it has not worked properly.
Is this happening as I don’t have actual data for those months?
Ok this isn’t too difficult.
Here’s a few ideas around projecting trends/numbers forward repeatedly.
In terms of your cumulative total.
Check out the technique showcased here using SUMX inside the CALCULATE function.
This is the exactly technique you need to use to get your cumulative total working.
See how you go with this.
Thanks
Sam
1 Like
Awesome, thanks!
I managed to solve my problem 
I took a look at the video and I am unclear on what is determining the forecasted value. Could you elaborate on the Dax? I have a similar task where I am being asked to forecast revenue for the rest of the year based on the last nbr of x weeks.
Thanks - Steve
In reality there are many ways you can create a forecast or represent one in Power BI.
In this example it’s purely just a projection of the same results over and over again going forward
But you could project results forward in a few ways, especially using time intelligence functions.
Here’s a whole module dedicated to concepts like this.
If you have a specific requirement you don’t think is covered in here just add the question to a new forum thread and we can have a look at it further.
Thanks
Sam
