Dynamically Get last 30 , 60 and 90 days from month

[adarsh2608]

Hello I need to  summarize the excel (attached) here it should be group by year, and group 

expected o/p as below. it should sum the values and update it in the each column respectively for year and group . values and year are dynamic which may change every year . last 30 ,60,90 and 120 days should show the month based. as in the year column there is only month and year .

so in last 30 days it should show data of entire previous month ,even if current day is 1st of May it should show all the data of April 
and similarly if it is last 60 days it should show last 2 months data and 90 days is last 3 months data

GROUP

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

Last 30 Days

Last 90 Days

Last120 Days

Direct Fund

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 )

Asset

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 Fund

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 Support

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 )

[binuacs]

@adarsh2608 can you also provide the expected result in an excel file based on your input file?

[adarsh2608]

Hello,
Below is the expected O/P

GROUP

2013

2014

2015

2016

2017

2018

2019

2020

2021

2022

Last 30 Days

Last 90 Days

Last120 Days

Direct Fund

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 )

Asset

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 Fund

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 Support

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 )

[RolandSchubert · Accepted solution]

Hi @adarsh2608 ,

"last 30 days" - you can usually solve problems like this using DateTime functions.

But - let's split the problem in two parts. First part is the calculation of  "year" columns (2013, 2014, 2015,...). To calculate these columns, you have to take the "year-part" from the [year] column. A Formula tool with a LEFT function may be helpful to do so. Then a Summarize tool can be used to calculate the total by group.

For the second part (calculation of last 30/60/90 days) I would use a Text Input tool to create a list of the columns, a sample entry for start and end of the period ranges and the number of months to include (in addition to the last one).

DateTimeFunctions can be used to calculate the start and end for the 3 ranges - DateTimeFirstOfMonth e.g. returns the first day of the current month, using DateTimeAdd you can go one day back to get the last day of the previous month - that's the end of all 3 period ranges.  The same logic can be used to calculate the start  - it's one/two/three months back.

Using Append Fields you can add all these ranges to your input data, select only the rows within the period ranges and aggregate by range and group.  For the comparison, the [Year] column has to be converted to a date using DateTimeParse.

Union both results and transpose are the final steps. 

I think, it should work that way. Give it a try and let me know, if it works for you or if there are any additional questions. 

Best,

Roland