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

 

Thanks for your reply.

 

Actually, Monthly AV is not a column but a measure calculated based on the start date and end date of the project. Thus there is no table having the following schema (Period, Monthly AV).

 

I've gone through that article, but it seems that those use cases are on the premise that all records are on a daily basis like sales. However, in my case, the pain is that project is in progress lasting for months or even years.

 

It's really appreciated though, and I would be very grateful if you could give me other advice.

 

Cheers

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

starmoonknight
Because your example data is elliptic, so I use the result data as data resurce. If the Monthly AV is a measure, you can reproduce the calculated column as measure, the measure Monthly AV  can be nested. Or could you please post the whole sample data for further analysis?

Best Regards,
Angelia

  • 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,