Calculate Value for Reference Date

[Anonymous]

Hi,

we use a manually crafted table with specific reference dates (previous year) for every day and every business location/CC.

I'm able to map those reference dates onto my facts table (via 'key' "CC-Date"), but im not yet able to calculate the reference revenue in the same row as the actual revenue.

What I'm looking for:

Date	Revenue	RefDate	RefRevenue?	Location/CC (i think you can ignore this field for answering the question)
01.01.2019	235135	01.01.2018	126513 (=Revenue from RefDate)	A/1500
01.01.2019	218952	03.01.2018	161341	B/2300
02.01.2019	219925	03.01.2018	104465	A/1400

The difficulty comes with the underlying table consisting of rows that are based on the revenue per article:

Date	Revenue	ArticleID	Location/CC
01.01.2019	150	15	A/1500
01.01.2019	30	18	A/1400
01.01.2018	140	15	A/1500

The Articles are needed for filter purposes.

I'm running circles in my head, but i think the solution might be very simple.

Thanks!

Best Regards

Karl

[formosasol]

Hi Karl,
Can you provide some demo data to look at.

Regards
Frank

[Anonymous]

I attached a txt-file with tab as delimiter. 

Thats how the source table looks like. 

In the example file you can find three dates from 2019: 01, 02 and 03 May and the corresponding RefDates 01, (02) 03 and 04 May 2018.

In this case all 2019 dates have only one corresponding date in 2018, but it's also possible, that there is a different refdate for every CC. 

I dont need CC and ArticleID in the final table, but i need to filter on them. On the Date-Dimension, I'm only filtering on the Date, not the RefDate.

I guess this result is only achievable via set analysis and some aggr() usage?! Thanks alot!