This post is related to a previous post called “Project Revenue Forecasting”. As a refresher, I was attempting to allocate revenue on a daily basis over two dates within one table. The solution (of which a few videos have been created and were helpful) was to create a virtual date table in order to allocate between dates. I have been able to get that to work. However, I have been trying to take it further in order to create cumulative totals on a monthly and yearly basis to produce charts and table showing projected revenue forecast. The issue comes with the yearly cumulative forecast and getting the message in Power BI “Visual has exceeded available resources”. Below are the formulas I’m using in order to create the cumulative totals:
The first formula comes from the previous post and is used to allocate the revenue on a daily basis:
Est. Wt. Revenue per Day =
CALCULATE( SUMX( ‘Practice Participations’, ‘Practice Participations’[Daily Revenue] ),
FILTER( VALUES( Opportunities[Est. Project Start Date] ), Opportunities[Est. Project Start Date] <= MAX( ‘Virtual Dates’[Date] ) ),
FILTER( VALUES( Opportunities[Est. Project End Date] ), Opportunities[Est. Project End Date] >= MIN( ‘Virtual Dates’[Date] ) ) )
I then created a formula that counts the days of the project and can be broken down based on context:
Duration (Days) =
CALCULATE( COUNTAX( ‘Virtual Dates’, [Est. Wt. Revenue per Day] ),
FILTER( VALUES( ‘Virtual Dates’ ), ‘Virtual Dates’[Date] < MAX( Opportunities[Est. Project End Date] ) ) )
Then I created a product of the two in order to obtain the project revenue sum that has now been assigned per day and can therefore yield partial results based on context:
Total Est. Wt. Revenue = [Duration (Days)] * [Est. Wt. Revenue per Day]
Lastly, I use this in order to create the cumulative totals formula:
Yearly Opp. Revenue = CALCULATE( SUMX( ‘Practice Participations’, [Total Est. Wt. Revenue] ), DATESYTD( ‘Virtual Dates’[Date] ) )
The issue I have is this calculation takes too long and I get the error message. Initially the first time I run it, I have no issue and am able to obtain a result. After any changes though, it will then proceed to give me the error message. My understanding of the main problem is the virtual relationship being rather resource intensive. I’m looking to help simplify this in order to get a result quicker. Also, I have filtered the data to the smallest possible set I can, but it is still too large for the method I have listed above.
After searching within Microsoft forums, I came across a promising solution. If I were to type those measures as variables and create a single measure, I am able to produce a result with virtually no delay. The result however doesn’t make sense and I suspect it has to do with the nature of having a virtual relationship within a variable. Is there a way to type the above measures as variables and get the cumulative result? Or are there simpler measures that are less resource intensive that can yield a result in a reasonable amount of time without exceed the resource limit?
Thank you for any insights you might be able to provide.