Forum Discussion

starmoonknight's avatar
starmoonknight
Helper II
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
    starmoonknight
    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's avatar
starmoonknight
Helper II
9 years ago

Hi v-huizhn-msft

 

As I've kind of broken down the problem into small pieces (in the real scenario, I've got more calculated columns and measures), sample data of the model is like this 


Project:


 
 
 

 

 

Where Added Value/Month is a calculated column based on Added Value and Duration 
 

TimeDimension and Date here is continuous:


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 


Expected result


 

 

 

 

 

 

 

 

Where Monthly AV is the sum of Added Value/Month of all the projects that are in progress in the selected period (formula attached below), while Accumulated AV is the YTD Monthly AV

 

Monthly AV is a customer measure:

Total Monthly Added Value = 
CALCULATE(
    SUM([Added Value/Month]),  
    FILTER(
        Project, 
        Project[Start Date] <= MAX(TimeDimension[Date]) 
    ),  
    FILTER(
        Project,  
        Project[End Date] >= MIN(TimeDimension[Date]) 
    )
)

Basically, it identifies projects that are in progress during a specific period by comparing the start and end date of the project to the period start/end. If the project starts before or on period end and ends after or on period start, then it is considered as in progress. If the duration of a project is N, then its Added Value/Month should appear in N period.

 

I'm not sure what do you actually mean by nested, but to calculate the sum of this measure does not work as the result is the sum of all the monthly AV of projects ever active up to date rather than the accumulated AV of projects active in each month.

 

This is the result of a measure trying to replace period start with year start, and I've explained it in previous reply.

 

Thanks in advance

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

starmoonknight

Sorry for delay response, could you share us the solution for getting below result for further analysis?



Thanks a lot,
Angelia

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    Hi v-huizhn-msft

     

    Sorry to have made you confused. This column is just to explain which projects are taken into account when getting the total monthly added value.

     

    What I'm able to get now is just the monthly added value, which is a custimsed measure, by period. 

     

    Cheers,