Last twelve month calculation under irregular timeseries and various scenarios

Question

Hi community,

I need to calculate last twelve month value of a measure given some conditions. But this calculation should be added as a row with a new dimension member of LTM. Below picture simply illustrates the calculation logic. For Quarter case LTM equals 'latest quarter value' + 'latest year value' - 'previous year quarter value'. In other words 3m + 12m - 3m. For half-year case LTM equals 'latest half-year value' + 'latest year value' - 'previous year half-year value'. In other words 6m + 12m - 6m

But in order to calculate LTM, multiple conditions need to be satisfied. There must be previous period in last year value and last full year value. On top of that, all these values should be in same currency. The challenge is that data is not regular as I do not have always quarter, half-year, year, etc. records. It changes from company to company.

I created below sample input data. My data initially does not include these NULL months. I create those to facilitate rolling if applicable. Let me further clarify case by case:

* CompanyA June 2018 period is simple to implement. LTM can be calculated for June 2018 as June 2017 half-year and December 2017 full-year is available in USD terms. LTM sales value is June 2018 + December 2017 - June 2017. Please note that I do not need LTM Sales as a new column. Instead, I need as a new row where Period is LTM.
* But, CompanyA March 2019 period is not ideal to calculate LTM as same period in last year (March 2018) is missing. Whereas, June 2019 period satisfies LTM conditions.
* Please also note that December is not always 'Year'. The data should not be taken into account as calendar based. Some companies have year-end a.k.a 'Year' other than December. As you can see CompanyB case. year-end is June and so half-year is December. However, December 2018 period does not satisfy LTM condition due to no previous period.
* CompanyD June 2019 period is suitable to calculate LTM. However, June 2020 is not ideal to calculate. Although this period satisfies required periods conditions, it fails to satisfy currency condition, as currency should be same for all periods subject to LTM.
* CompanyE March 2018, June 2018, and September 2018 are ideal to calculate LTM. However September 2018 is tricky here. As this period is Q3 and covers only three months. So, in this case LTM = Q3 + H1 + Year - Q3 - H1
* September 2019 is again tricky. It already covers 9 months, so I need 9 months coverage in previous year. In this case LTM = YTD + Year - Q3 - H1

As you can see the data is quite irregular:

* Not all companies have December year-end (Year)
* Interims are not stable. Q1, Q2, and Q3 as quarters (3months range) H1 as half year (6months range), YTD (9months range)
* Currency can be changing across periods

CompanyDatePeriodFXSalesCompanyA31.12.2016YearUSD1308CompanyA31.01.2017   CompanyA28.02.2017   CompanyA31.03.2017   CompanyA30.04.2017   CompanyA31.05.2017   CompanyA30.06.2017H1USD1767CompanyA31.07.2017   CompanyA31.08.2017   CompanyA30.09.2017   CompanyA31.10.2017   CompanyA30.11.2017   CompanyA31.12.2017YearUSD3680CompanyA31.01.2018   CompanyA28.02.2018   CompanyA31.03.2018   CompanyA30.04.2018   CompanyA31.05.2018   CompanyA30.06.2018H1USD1892CompanyA31.07.2018   CompanyA31.08.2018   CompanyA30.09.2018   CompanyA31.10.2018   CompanyA30.11.2018   CompanyA31.12.2018YearUSD4100CompanyA31.01.2019   CompanyA28.02.2019   CompanyA31.03.2019Q1USD2823CompanyA30.04.2019   CompanyA31.05.2019   CompanyA30.06.2019H1USD8511CompanyA31.07.2019   CompanyA31.08.2019   CompanyA30.09.2019   CompanyA31.10.2019   CompanyA30.11.2019   CompanyA31.12.2019YearUSD6494CompanyA31.01.2020   CompanyA29.02.2020   CompanyA31.03.2020   CompanyA30.04.2020   CompanyA31.05.2020   CompanyA30.06.2020H1USD3714CompanyB30.06.2017YearEUR165CompanyB31.07.2017   CompanyB31.08.2017   CompanyB30.09.2017   CompanyB31.10.2017   CompanyB30.11.2017   CompanyB31.12.2017   CompanyB31.01.2018   CompanyB28.02.2018   CompanyB31.03.2018   CompanyB30.04.2018   CompanyB31.05.2018   CompanyB30.06.2018YearEUR2242CompanyB31.07.2018   CompanyB31.08.2018   CompanyB30.09.2018   CompanyB31.10.2018   CompanyB30.11.2018   CompanyB31.12.2018H1EUR3019CompanyB31.01.2019   CompanyB28.02.2019   CompanyB31.03.2019   CompanyB30.04.2019   CompanyB31.05.2019   CompanyB30.06.2019YearEUR5940CompanyC30.06.2020H1EUR140CompanyC31.07.2020   CompanyC31.08.2020   CompanyC30.09.2020Q3EUR85CompanyD30.06.2018H1USD798CompanyD31.07.2018   CompanyD31.08.2018   CompanyD30.09.2018   CompanyD31.10.2018   CompanyD30.11.2018   CompanyD31.12.2018YearUSD2056CompanyD31.01.2019   CompanyD28.02.2019   CompanyD31.03.2019   CompanyD30.04.2019   CompanyD31.05.2019   CompanyD30.06.2019H1USD2200CompanyD31.07.2019   CompanyD31.08.2019   CompanyD30.09.2019   CompanyD31.10.2019   CompanyD30.11.2019   CompanyD31.12.2019YearEUR4778CompanyD31.01.2020   CompanyD29.02.2020   CompanyD31.03.2020   CompanyD30.04.2020   CompanyD31.05.2020   CompanyD30.06.2020H1USD2494CompanyE31.12.2016YearEUR4591CompanyE31.01.2017   CompanyE28.02.2017   CompanyE31.03.2017Q1EUR1214CompanyE30.04.2017   CompanyE31.05.2017   CompanyE30.06.2017H1EUR2493CompanyE31.07.2017   CompanyE31.08.2017   CompanyE30.09.2017Q3EUR1333CompanyE31.10.2017   CompanyE30.11.2017   CompanyE31.12.2017YearEUR5177CompanyE31.01.2018   CompanyE28.02.2018   CompanyE31.03.2018Q1EUR1406CompanyE30.04.2018   CompanyE31.05.2018   CompanyE30.06.2018H1EUR2855CompanyE31.07.2018   CompanyE31.08.2018   CompanyE30.09.2018Q3EUR1546CompanyE31.10.2018   CompanyE30.11.2018   CompanyE31.12.2018YearEUR6357CompanyE31.01.2019   CompanyE28.02.2019   CompanyE31.03.2019   CompanyE30.04.2019   CompanyE31.05.2019   CompanyE30.06.2019   CompanyE31.07.2019   CompanyE31.08.2019   CompanyE30.09.2019YTDEUR5419CompanyE31.10.2019   CompanyE30.11.2019   CompanyE31.12.2019YearEUR7419CompanyF31.12.2018YearGBP5299CompanyF31.01.2019   CompanyF28.02.2019   CompanyF31.03.2019Q1GBP1398CompanyF30.04.2019   CompanyF31.05.2019   CompanyF30.06.2019H1GBP2638CompanyF31.07.2019   CompanyF31.08.2019   CompanyF30.09.2019YTDGBP3849CompanyF31.10.2019   CompanyF30.11.2019   CompanyF31.12.2019YearGBP5332CompanyF31.01.2020   CompanyF29.02.2020   CompanyF31.03.2020Q1GBP1970CompanyF30.04.2020   CompanyF31.05.2020   CompanyF30.06.2020H1GBP4397CompanyF31.07.2020   CompanyF31.08.2020   CompanyF30.09.2020YTDGBP5661

Desired output to be added back to original data:

CompanyDatePeriodFXSalesCompanyA30.06.2018LTMUSD3805CompanyA30.06.2019LTMUSD1697CompanyD30.06.2019LTMUSD3458CompanyE31.03.2018LTMEUR5369CompanyE30.06.2018LTMEUR5539CompanyE30.09.2018LTMEUR5752CompanyE30.09.2019LTMEUR7375CompanyF31.03.2020LTMGBP5904CompanyF30.06.2020LTMGBP7091CompanyF30.09.2020LTMGBP7144

I think it requires Batch Macro kind of approach. I am quite new and not much sophisticated to be honest. Any guidance would be deeply appreciated.

Thanks a lot

BenMoss · Answer

Hi @onanm it seems like the community was busy yesterday and your post slipped through the net, I imagine this may be down to the complexity of the problem and the time it may take someone to jump in and help.

I think we can potentially make your problem a bit clearer which may encourage more people to take a look. Specifically, it may be helpful to add, into your desired output table, three new columns, one containing the calculation used in it's raw form (Q1+H1....), one containing the values that translates to (1+4-3...) and a third one, which indicates the row numbers from which that information was gathered.

This may make your problem just a bit easier to understand.

I don't think this is a batch macro problem though, so I wouldn't go looking down that hole (I mean you can and it may provide you with a solution, but it would be an unnecessary detour).

Ben