Cumulative total, where 1st month will be 0%, 2nd mth 30%, 3rd 60% & 4th mth onwards 100%

[piety]

Hi,

I have a ProjectWise data with projectID, ExpectedMonthlyRevenue and projectLiveDate

I want to calculate Monthly Running total, where 1st month revenue addition will be 0%, 2nd mth 30%, 3rd 60% & 4th mth onwards 100% and should get reset at the end of the year

[v-stephen-msft]

Hi @piety ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @piety ,

First you need to create a calendar and create a relationship between the calendar table and the main table

Table 2 = ADDCOLUMNS(CALENDAR(MIN('Table'[projectLiveDate]),MAX('Table'[projectLiveDate])),"Month",MONTH([Date]))

Secondly, create the following measure

Cumulative total = var _last=CALCULATE(SUM('Table'[ExpectedMonthlyRevenue]),FILTER(ALL('Table 2'),[Month]=MAX('Table 2'[Month])-1))
var _this=SUM('Table'[ExpectedMonthlyRevenue])
var _divide=DIVIDE(_this-_last,_last)
return IF(ISBLANK(_divide),0,_divide)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.