How to make a sum of monthly values from the beginning of the year?
I have a dataset containing data over the last 18 months for different references (in rows).
Each column header is defined using 'M-x' format ('M' stands for current month, wheras 'x' defines how many months ago) from M-18 to M-1.
Each month, I get a new 18-colmun dataset, where all values shift by 1 column to the left and the last column contains the value of the previous month (M-1).
I'd like to sum the total amount for each references (in rows), from the beginning of the current year whatever the current month is.
For instance, In April, I'll need to sum only the last 3 columns on the right of the dataset. And in September, I'll need to sum only the last 8 columns on the right of the dataset. Note In January it's a bit of exception , since I need to get the 12 last columns (the complete previous year indeed).
I've no idea how to start.
If anyone could help me. It would be much appreciated.
Would you be able to provide a couple example rows of the data format, i.e.:
Along with what the desired result should look like given that sample input data?
And then what does the input dataset look like after the new monthly data is added? From your description, it sounds like a Python recipe might be the best approach since the logic appears a little complex, but looking at a specific example of the data should help discuss approaches!