adding a constant to a sum() and accumulation over weeks/months

[Florian01]

Hello everyone,

I have following issue: 

I have a line graph with Sum(A) as formula and I also implemented a Mastercalender allowing me to have calender Weeks and Months as dimension).

I want to use the accumulation option of the graph to show Sum(a) over calender Week and respectively months. 

Now I have to add one constant K only one time to this sum so bascally an offset. 

If I use "=Sum(A) + K' the accumulation does not work correctly because K will be added for each Week/month. 

How to adapt the formula so that the constant K will be added only one time so will not be accumulated?

Chatgpt proposed me to solutions: 
Solution 1 (for months):

RangeSum(
Above(
Sum({<Month>} A),
0,
RowNo(TOTAL)
)
) + Only({1} K)

Solution 2:

Sum(<Month>} A) + Only({1} K)

none of them work. I get 0 over the whole timerange for solution 2 and an exponential curve going way too high for solution 1.

Would be very Happy if I could get some help on this one. 

Thanks 🙂

Best Regards,

Florian

[TauseefKhan]

Hi @Florian01,

SET K = 10; // Adjust the value here accordingly

Ensure the Master Calendar is correctly implemented with Weeks and Months as dimensions.
Use the accumulation and add the constant ( K ) accordingly.

Weeks:
RangeSum(Above(Total Sum(A), 0, RowNo(TOTAL))) + if(RowNo(TOTAL) = 1, $(K), 0)

Months:
RangeSum(Above(Total Sum(A), 0, RowNo(TOTAL))) + if(RowNo(TOTAL) = 1, $(K), 0)

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[Florian01]

thanks for the fast Reply 🙂 unfortunaltey with this solution I get the exponentional behavior from the solution 1 from ChatGpt.

[Florian01 · Accepted solution]

ok I solved it with Sum(A) +  if(RowNo(TOTAL) = 1, $(K), 0)

Thanks for the help!