I’m having a bit of difficulty with a specific area of a topic. I’ve seen several examples in the budgeting and forecasting videos, but I’m still having difficulty changing the granularity of the information to make it usable.
The information we have for projects are total estimated revenue, start date, and duration. I’m trying to calculate monthly revenue for a forecasting model and I need to break down the total estimated project revenue over the period of time of the project. The examples I’ve seen already have budgets assigned to discreet periods of time and I’ve been able to replicate those formulas. The challenge I have is getting my data to a usable granularity to use the measures for the forecast. I’m uncertain if I can perform this within a measure or need to generate a new table.
I believe I will ultimately end up with the following measure:
Budget Allocation = VAR DaysinContext = COUNTROWS( Dates ) VAR DaysinMonth = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year] )) VAR CurrentMonth = SELECTEDVALUE( Dates[MonthName] ) VAR MonthlyBudgetAmounts = CALCULATE( [Total Budgets], FILTER( ALL( 'Product Budgets'[MonthName] ) , CurrentMonth = 'Product Budgets'[MonthName] ) ) RETURN IF( OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Month & Year] ) ), DIVIDE( DaysinContext, DaysinMonth, 0 ) * MonthlyBudgetAmounts, [Total Budgets] )
The part I’m having difficulty with is the Total Budgets portion of the measure to be able to get results.
Thanks for any help you can provide!