Forum Discussion
starmoonknight
9 years agoHelper II
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. ...
- 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.
starmoonknight
9 years agoHelper II
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,