ideas for automatization of the file

Hi there,

I would recommend thinking about additional years as well as months. Also, since the PivotTable is based on the Table on Arkusz1, and they don't refresh automatically, your formulas should point to Arkusz1 instead of Arkusz2. The first thing you need to do is define the calculations you are wanting to make.

To start, you can dispense with copying/pasting the PivotTable data to Arkusz3. Instead you need to change the cells in columns B through Y into formulas which will return the lastest months data, including the three prior months, since those are what your calculations are looking at. To do this, it will be easier with the help of a few named formulas, to assist in calculating the latest/prior dates.

Define four names.

Name: LatestDate1

Formula: =EOMONTH(MAX(Tabela1[Value Date]),-1)+1

Name: LatestDate2

Formula: =EOMONTH(MAX(Tabela1[Value Date]),-2)+1

Name: LatestDate3

Formula: =EOMONTH(MAX(Tabela1[Value Date]),-3)+1

Name: LatestDate4

Formula: =EOMONTH(MAX(Tabela1[Value Date]),-4)+1

If you don't know how to create these, it's the 'Define Name' button on the 'Data' ribbon tab. Or use the keyboard shortcut CTRL+F3, then click 'New'.

Once you have these names in place, you can use the formulas below to return the desired data. Enter these in cells B2:Q2. Once they're all in, select B2:Q2, hover your mouse over the bottom-right corner until your cursor turns into a bold "+" sign, then drag down five rows.

B2: =SUMIFS(Tabela1[ Volume $] ,Tabela1[Year],YEAR(LatestDate4),Tabela1[Month],MONTH(LatestDate4),Tabela1[Customer],$A2)

C2: =SUMIFS(Tabela1[Revenue $],Tabela1[Year],YEAR(LatestDate4),Tabela1[Month],MONTH(LatestDate4),Tabela1[Customer],$A2)

D2: =COUNTIFS(Tabela1[Year],YEAR(LatestDate4),Tabela1[Month],MONTH(LatestDate4),Tabela1[Customer],$A2)

E2: =SUMIFS(Tabela1[uniq_cur],Tabela1[Year],YEAR(LatestDate4),Tabela1[Month],MONTH(LatestDate4),Tabela1[Customer],$A2)

F2: =SUMIFS(Tabela1[ Volume $] ,Tabela1[Year],YEAR(LatestDate3),Tabela1[Month],MONTH(LatestDate3),Tabela1[Customer],$A2)

G2: =SUMIFS(Tabela1[Revenue $],Tabela1[Year],YEAR(LatestDate3),Tabela1[Month],MONTH(LatestDate3),Tabela1[Customer],$A2)

H2: =COUNTIFS(Tabela1[Year],YEAR(LatestDate3),Tabela1[Month],MONTH(LatestDate3),Tabela1[Customer],$A2)

I2: =SUMIFS(Tabela1[uniq_cur],Tabela1[Year],YEAR(LatestDate3),Tabela1[Month],MONTH(LatestDate3),Tabela1[Customer],$A2)

J2: =SUMIFS(Tabela1[ Volume $] ,Tabela1[Year],YEAR(LatestDate2),Tabela1[Month],MONTH(LatestDate2),Tabela1[Customer],$A2)

K2: =SUMIFS(Tabela1[Revenue $],Tabela1[Year],YEAR(LatestDate2),Tabela1[Month],MONTH(LatestDate2),Tabela1[Customer],$A2)

L2: =COUNTIFS(Tabela1[Year],YEAR(LatestDate2),Tabela1[Month],MONTH(LatestDate2),Tabela1[Customer],$A2)

M2: =SUMIFS(Tabela1[uniq_cur],Tabela1[Year],YEAR(LatestDate2),Tabela1[Month],MONTH(LatestDate2),Tabela1[Customer],$A2)

N2: =SUMIFS(Tabela1[ Volume $] ,Tabela1[Year],YEAR(LatestDate1),Tabela1[Month],MONTH(LatestDate1),Tabela1[Customer],$A2)

O2: =SUMIFS(Tabela1[Revenue $],Tabela1[Year],YEAR(LatestDate1),Tabela1[Month],MONTH(LatestDate1),Tabela1[Customer],$A2)

P2: =COUNTIFS(Tabela1[Year],YEAR(LatestDate1),Tabela1[Month],MONTH(LatestDate1),Tabela1[Customer],$A2)

Q2: =SUMIFS(Tabela1[uniq_cur],Tabela1[Year],YEAR(LatestDate1),Tabela1[Month],MONTH(LatestDate1),Tabela1[Customer],$A2)

Your formulas in R:Z can stay the same. Once you've done the above, columns N:Q will always be the latest months data from Arkusz1, columns J:M will be one month prior, columns F:I will be two months prior, and columns B:E will be three months prior.

To show you how the results match what you have, I've adjusted your sample file and uploaded it with the above names and formulas in place.