Return Column Value based on Current Month

SteveC

Hi All,

Looking for some help on what I feel like you be a very simply formula, that I am over complicating.

We track revenue by program throughout the year (contracted rev, delivered rev, billable rev, for Jan - Dec) For our dashboards I am trying to have one column for "this month's contracted rev", "This month's delivered rev" etc, that changes based on a date reference so that each time the month changes I don't have to update dashboard to the current month, it will just pull in automatically.

Below is my unsuccessful attempt at this, so I am hoping someone has some advice to help us out.

=IF({This Month Number} = 1, [Jan Billable Rev USD]@row), IF({This Month Number} = 2, [Feb Billable Rev USD]@row), IF({This Month Number} = 3, [March Billable Rev USD]@row), IF({This Month Number} = 4, [April Billable Rev USD]@row), IF({This Month Number} = 5, [May Billable Rev USD]@row), IF({This Month Number} = 6, [June Billable Rev USD]@row), IF({This Month Number} = 7, [July Billable Rev USD]@row), IF({This Month Number} = 8, [Aug Billable Rev USD]@row), IF({This Month Number} = 9, [Sept Billable Rev USD]@row), IF({This Month Number} = 10, [Oct Billable Rev USD]@row), IF({This Month Number} = 11, [Nov Billable Rev USD]@row), IF({This Month Number} = 12, [Dec Billable Rev USD]@row)

SteveC

Not sure if this is the right way, but I was able to make this work with the formula below.

=IF({This Month Number} = 7, SUM([July Billable Rev USD]@row:[July Billable Rev USD]@row), IF({This Month Number} = 6, SUM([June Billable Rev USD]@row:[June Billable Rev USD]@row), IF({This Month Number} = 5, SUM([May Billable Rev USD]@row:[May Billable Rev USD]@row), IF({This Month Number} = 4, SUM([April Billable Rev USD]@row:[April Billable Rev USD]@row), IF({This Month Number} = 3, SUM([March Billable Rev USD]@row:[March Billable Rev USD]@row), IF({This Month Number} = 2, SUM([Feb Billable Rev USD]@row:[Feb Billable Rev USD]@row), IF({This Month Number} = 1, SUM([Jan Billable Rev USD]@row:[Jan Billable Rev USD]@row), IF({This Month Number} = 8, SUM([Aug Billable Rev USD]@row:[Aug Billable Rev USD]@row), IF({This Month Number} = 9, SUM([Sept Billable Rev USD]@row:[Sept Billable Rev USD]@row), IF({This Month Number} = 10, SUM([Oct Billable Rev USD]@row:[Oct Billable Rev USD]@row), IF({This Month Number} = 11, SUM([Nov Billable Rev USD]@row:[Nov Billable Rev USD]@row), IF({This Month Number} = 12, SUM([Dec Billable Rev USD]@row:[Dec Billable Rev USD]@row)))))))))))))

SteveC

Not sure if this is the right way, but I was able to make this work with the formula below.

=IF({This Month Number} = 7, SUM([July Billable Rev USD]@row:[July Billable Rev USD]@row), IF({This Month Number} = 6, SUM([June Billable Rev USD]@row:[June Billable Rev USD]@row), IF({This Month Number} = 5, SUM([May Billable Rev USD]@row:[May Billable Rev USD]@row), IF({This Month Number} = 4, SUM([April Billable Rev USD]@row:[April Billable Rev USD]@row), IF({This Month Number} = 3, SUM([March Billable Rev USD]@row:[March Billable Rev USD]@row), IF({This Month Number} = 2, SUM([Feb Billable Rev USD]@row:[Feb Billable Rev USD]@row), IF({This Month Number} = 1, SUM([Jan Billable Rev USD]@row:[Jan Billable Rev USD]@row), IF({This Month Number} = 8, SUM([Aug Billable Rev USD]@row:[Aug Billable Rev USD]@row), IF({This Month Number} = 9, SUM([Sept Billable Rev USD]@row:[Sept Billable Rev USD]@row), IF({This Month Number} = 10, SUM([Oct Billable Rev USD]@row:[Oct Billable Rev USD]@row), IF({This Month Number} = 11, SUM([Nov Billable Rev USD]@row:[Nov Billable Rev USD]@row), IF({This Month Number} = 12, SUM([Dec Billable Rev USD]@row:[Dec Billable Rev USD]@row)))))))))))))