Rolling 12 month column chart

Luka

I have a column chart as seen below:

The summary sheet i use as the data source is formatted as below:

I used a summary sheet instead of a summary report as it gives me more options to manipulate the data, but also structure it correctly in terms of month order.

The months and years will have to manually be inputted and the formulas in the final 3 columns will have to be dragged down. However, is there a way to automate the chart widget/ summary sheet so that it is only the final 12 months in the summary sheet that get included in the dashboard widget, or will this have to manually be selected each month?

Thank you in advance.

Michelle Choate 2

So with the formula that he gave you, it is checking everything that is greater than 12 months ago. So you just need to add an AND Statement in there.

=IF(AND(DATE(Year@row, [Month Number]@row, 1) >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), DATE(Year@row, [Month Number]@row, 1) <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), 1)

Paul Newcome

@Luka Sorry about that. I got ahead of myself.

=IF(AND(DATE(Year@row, [Month Number]@row, 1) >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), DATE(Year@row, [Month Number]@row, 1) <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), 1)

Paul Newcome

You can include a helper checkbox column with the following formula:

=IF(DATE(Year@row, [Month Number]@row, 1) >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), 1)

Then create a row report that pulls from this sheet and only displays rows where this helper column is checked. Finally use that report to feed your chart.

Luka

Hi Paul, thank you for your response. I agree that something similar to this could the best way forward. However, when i add the helper column and formula, all the rows seem to be checked for some reason. Do you know why this might be? I should have previously made this clearer but i only want it to show/ tick the previous 12 months YTD (e.g. Feb25 would show Feb24-Jan25 in the chart)

Michelle Choate 2

So with the formula that he gave you, it is checking everything that is greater than 12 months ago. So you just need to add an AND Statement in there.

=IF(AND(DATE(Year@row, [Month Number]@row, 1) >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), DATE(Year@row, [Month Number]@row, 1) <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), 1)

Paul Newcome

@Luka Sorry about that. I got ahead of myself.

=IF(AND(DATE(Year@row, [Month Number]@row, 1) >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), DATE(Year@row, [Month Number]@row, 1) <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)), 1)