Forum Discussion

starmoonknight's avatar
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
    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.