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.

 

Current data model

Project

...

Start Date

End Date

Added Value

Duration (in month)

Added Value/Month

 

TimeDimension

Date

Period

 

 

I've successfully got the total monthly added value using the following formula:

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

where PeriodStart =

PeriodStart = MIN(TimeDimension[Date])

and PeriodEnd =

PeriodEnd = MAX(TimeDimension[Date])

 This formula sums monthly added value of projects that are still active during that period, e.g. if a project lasts from 31 July to 1 Oct, it is considered in progress from July to Oct (duration = 4 months), thus its added value will be added to the total monthly added value.

 

However, when I try to get the accumulated one, it does not work if I simply change PeriodStart to the start of the year.

 

I'm not an expert of DAX, so could any one suggest whether there is way to get the YTD accumulated added value?

 

Thanks in advance!

 

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

     

     

     

16 Replies

    • 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

    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

    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.