# How to fix Dynamic cumulative total for 3 years trend line

## Recommended Posts

Hi All,

have the data like Budget Cost, Period (Year&Month), Month , Quarter. i am trying to show cumulatve total budget cost per Period (Year& Month) in trend line as per Year and Quarter. But As per my data - Budget cost is showing only for January month of the year and we are getting straight line insted of flat line as per the data.

for that alternatively - we are trying to splitting this Budget cost per each month based on below condition to get cumulative total as we are expecting

(ex: Jan : Budget cost/12* 1, Feb : Budget Cost/12 *2 ,,,,,,,,,,,,,,,,,Dec: Budget Cost/12*12). to implemet such conition i am using below calculation.

For showing cumulative Budget cost per YEAR: i am using below logic. (it is working fine as expected)

" Sum([bUDGET COST]) OVER (AllPrevious([Axis.X])) / 12 * Avg([MONTH]) "

For showing cumulative Budget cost per Quarter of the year: i am using below logic. (it is also working fine as expected)

" Sum([bUDGET COST]) OVER (AllPrevious([Axis.X])) / 4 * Avg([Quarter]) "

here i also need to fix this logic for 3 years cumulative budget cost per period however i implemeted for year & Quarter in above. it is working fine one year (Month wise /Quarter Wise) but unable to fix same logic for 3 Years cumulative budget cost (Month wise/Quarter wise)

PFA- Sample Dxp file.

kindly suggest how should i fix this.

Guru Sankar.

##### Share on other sites

Hi,

Have you tried to use the over function with multiple columnss Why don't you create a data saperate table for each usecase via add datatable with transformation

best regards

swen