Forum Discussion
Accumulate added 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.
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
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
Period This measure Projects in Progress
201508 3000 P2, P3
201509 4500 P2, P3, P4
201510 5500 P1, P2, P3, P4
201511 5500 P1, P2, P3, P4
201512 7500 P1, P2, P3, P4, P5
201601 3000 P2, P5
201602 3000 P2, P5
Cheers,