total by row on x number of columns

[anjumvahora]

Hi

I need to created a Total Revenue column that totals by Row. Each column represents a month. However, this changes each fiscal year (ours is from Oct-Sept). In the attached file, I've put all the months and at the moment, we calc this in a formula

(if IsNull([Oct. 2017]) then 0 else [Oct. 2017] endif) +
(if IsNull([Nov. 2017]) then 0 else [Nov. 2017] endif) +
(if IsNull([Dec. 2017]) then 0 else [Dec. 2017] endif) +
(if IsNull([Jan. 2018]) then 0 else [Jan. 2018] endif) +
(if IsNull([Feb. 2018]) then 0 else [Feb. 2018] endif) +
(if IsNull([Mar. 2018]) then 0 else [Mar. 2018] endif) +
(if IsNull([Apr. 2018]) then 0 else [Apr. 2018] endif) +
(if IsNull([May 2018]) then 0 else [May 2018] endif) +
(if IsNull([Jun. 2018]) then 0 else [Jun. 2018] endif) +
(if IsNull([Jul. 2018]) then 0 else [Jul. 2018] endif) +
(if IsNull([Aug. 2018]) then 0 else [Aug. 2018] endif) +
(if IsNull([Sep. 2018]) then 0 else [Sep. 2018] endif)

But I want to be able to calculate this without specifying the cols as next month, the data will only show for Oct 18 and then increase by each month till the end of the year.

How can I do this?

[jdunkerley79]

One solution, is to use a record ID and then transpose to switch to rows. You can then summarise and join back.

Sample attached.