Forum Discussion

starmoonknight's avatar
starmoonknight
Helper II
9 years ago
Solved

Accumulate added value

Dear all   I'm got stuck while calculating accumulated added value for forecasting purpose. Our company is project oriented, thus it seems a little bit complicated to get the accumulated value.   ...
  • starmoonknight's avatar
    starmoonknight
    9 years ago

    Managed to solve the problem finally!

     

    Not necessary to be an efficient solution, but at least it works after so many trials.

     

    In a nutshell, I'm using the monthly added value times the progressed month to get the accumulated added value for each project and sum all projects together.

     

     

    Accumulated Added Value = 
SUMX(VALUES(Project[Number]), [Progressed Month] * [Active Monthly Added Value])

     

    where

     

    Progressed Month = 
CALCULATE(
	DISTINCTCOUNT('Date'[Period]),
	FILTER(
		'Date',
		'Date'[Date] >= 
		MAX(
			[FiscalYear Start], 
			MIN(Project[Start Date])
		)
		&& 'Date'[Date] <= 
		MIN(
			[PeriodEnd], 
			MAX(Project[Completion Date])
		) 
	)
)

     

    and

      

    Active Monthly Added Value = 
CALCULATE(
	SUM([Added Value/Month]), 
	FILTER(
		Project, 
		Project[Start Date] <= [FiscalYearEnd]
	), 
	FILTER(
		Project, 
		Project[Start Date] <= [PeriodEnd]
	)
)

     

    and the visual looks like this (colours are used for prject types). Hope this would help those with similar problems.

     

     

     

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago
Total Monthly Added Value = 
CALCULATE(
	SUM([Added Value/Month]), 
	FILTER(all(
		Project), 
		Project[Start Date] <= MAX(TimeDimension[Date])
	), 
	FILTER(
		all(Project), 
		Project[End Date] >= startofyear
	)
)

Basically, i

starmoonknight's avatar
starmoonknight
Helper II
9 years ago

Hi Vvelarde
 
I do have a YearStart measure and have tried this way, but unfortunately it doesn't work, as it just sums all the monthly AV of projects that are ever active during the year, but not accumulates the monthly AV for each project.
 
The calculation result of this measure would look like this


 
 
 
 
 
 
 
 
 
A possible way is to introduce another measure which is the YTD duration (number of months) that can multiply the monthly AV to get the accumulated AV, but I haven't figured out how.
 
Cheers,