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.
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.