Accumulate added value

[starmoonknight]

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 · Accepted solution]

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]

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,
 

[renaudgaillard]

Hi starmoonknight,

Maybe this link could help ?

http://www.daxpatterns.com/cumulative-total/

BR

[starmoonknight]

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,

[v-huizhn-msft]

@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]

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]

@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]

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]

@starmoonknight

Sorry for delay response, could you share us the solution for getting below result for further analysis?

Thanks a lot,
Angelia

[starmoonknight]

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,

[Vvelarde]

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]

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,
 

[starmoonknight]

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]

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]

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]

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,