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

       

       


       

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

    starmoonknight
    Please the accumulated AV for each fiscal year based on the Monthly AV column.

    First, please create several calculated column to display year, month, rank by month in each year.

     

    Year = LEFT('Table'[Period],4)
    Month = RIGHT('Table'[Period],2)
    Rank = RANKX(FILTER('Table',EARLIER('Table'[Year])='Table'[Year]),'Table'[Month],,ASC,DENSE)


    Then use below formula to calculate the accumulated AV for each fiscal year, and get expected result, please review below screenshot.

     

    Accomulated AV = CALCULATE(SUM('Table'[Monthly AV]),FILTER(ALLEXCEPT('Table','Table'[Year]),'Table'[Rank]<=EARLIER('Table'[Rank])))

     



    For more details about cumulative Total, please review this article.


    Best Regards,
    Angelia

     

    • 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