Forum Discussion
starmoonknight
Helper II
10 years agoAccumulate 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
Helper II
10 years agoA 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,