Dynamic : caluclating current month, Previous month, Last 2nd month from last 10 year data

Question

Hello ,
 I have Last 10 year data currently from (2013- 2022 which changes as next year it will be 2014-2023 )  i need to create a column and sum of values of last 10 year and sum values  for current month , last month  , last 2nd month, last 3rd month then last 4th month and 5th more .

for example if i'm running report in July then i need to get data from month July, June , May , April ,March And final column will be sum of  ( Feb + Jan) values. in the next month(which is August) last 5th month value will be sum of March to Jan. so here column will start from 2013 to 2021 .Instead of 2022 it will be  current month , last month  , last 2nd month, last 3rd month , last 4th month then last 5th month and more .But  if i'm running workflow on 2023 May then it will 2014 to 2022 then instead of 2023 it will be May, April, March , Feb,Jan and if i'm running workflow on 2023 June then June May, April, March , (Feb +Jan)

Sample Output

When running workflow on May

GROUP201320142015201620172018201920202021Jan (2022)Feb(2022)March(2022)April(2022)May(2022)Direct FundSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )AssetSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Mutual FundSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of value Tech SupportSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )

and sample workflow if it is running on July

GROUP201320142015201620172018201920202021sum(jan to March) (2022)April(2022)May(2022)June(2022)July(2022)Direct FundSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )AssetSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Mutual FundSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of value Tech SupportSum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )Sum of (value )

Sample input uploaded..

YearCaluculation.xlsx

adarsh2608 · Answer

Hello @BrandonB ,

Currently i have filter out the current year which will work but when i'm running next year may it require to show 2022 and filter out 2023 . manually it is not possible and how to setup formula so that in the next month when i run it will calculate Jan and Feb in the Jan(2022) column similarly when i run on July same column should calculate the sum of Jan+Feb+March , I have tried but not able to get for dynamic calculation for that column

BrandonB · Answer

I’d encourage you to give it a try! To get rid of the current year just use a filter before the part of the workflow where the years are crosstabbed and set it to filter out the current year. Filling in the missing months is a little more tricky but you can use a generate rows tool and increment with a datetimeadd function prior to the months getting crosstabbed.

Respond back with your attempt and the community can help you if you get stuck.

adarsh2608 · Answer

Hello @BrandonB ,

Thank you for the response .

but when i'm running next month ,current column name  will replace with Jan-Feb  (2022) and will be adding values of jan and feb . This will be going on  untill April 2023 after that when i'm running on May 2023 it will shown as now instead of 2022 it will be 2023 in the bracket. Currently column 2022 is not require as we are having month wise calcultaion in last 5 column so it should be from 2013 to 2021 then Jan(2022), Feb(2022),March(2022),April(2022), May(2022) Until May 2023 then column will be 2014 to 2022 Jan(2023), Feb(2023) March(2023) ,April(2023),May(2023) .

If i'm running worfklow on Jan 2023 then it should show columns of

2014-2021 then Jan- Sep(2022) , Oct(2022), Nov(2022) ,(Dec2022),Jan(2023)

And If there is no month in the report (excel) which is of last 5 column let say there is no month available for Feb then also column should be created for feb(2022) values can be 0 for all the group