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

 

Thanks renaudgaillard for sharing that article.

 

I guess the main problem in this data model is that a project is not a single transaction, which lasts over time. Thus there is no static DateKey available to map a project to a time period, but using start and end date to identify whether the project is in progress in that period.

 

Sample data of the model

Project

 

 

 

 

 

 

TimeDimension

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Date is continuous here

 

Expected result

 

 

 

 

 

 

 

 

 

 

Monthly AV is a customer measure here:

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.

 

The measure works as expected, but what's challenging is to get the accumulated AV for each fiscal year.

 

Thanks for your time

 

Cheers,

 

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago
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

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    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


     
     
     
     
     
     
     
     
     
    A possible way is to introduce another measure which is the YTD duration (number of months) that can multiply the monthly AV to get the accumulated AV, but I haven't figured out how.
     
    Cheers,
     

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    Thank you 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


     
     
     
     
     
     
     
     

     
    A possible way might be to introduce another measure which is the YTD duration (number of months) that can multiply the monthly AV to get the accumulated AV, but I haven't figured out how.
     
    Cheers,
     

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    Thanks you 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

     

     


     

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    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

     

     

    Cheers,
     

  • starmoonknight's avatar
    starmoonknight
    Helper II
    9 years ago

    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,