Forum Discussion

starmoonknight's avatar
starmoonknight
Icon for Helper II rankHelper II
10 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
    Icon for Helper II rankHelper 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
      Icon for Community Champion rankCommunity 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
        Icon for Helper II rankHelper 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,
         

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft 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
        Icon for Helper II rankHelper 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