Dynamic Formula for changing Colum Headers

[Dominic_Albans]

How do I create a formula for Daily Variance, given that the Colum headers / name changes every day/

(This data set will have a continuous cycle of 3 dates, with the oldest being removed, and the latest being added)

[apathetichell]

transpose - order your dates/previous column headers. change create values called day/day-1/day-2 in formula tool. use dynamic rename to match the [Name] column with your original date names to your original data stream - rename to day/day-1/day-2. use formula tool to perform calculations on static names (ie day/day-1/day-2) - if needed - remap original column names.

[binuacs]

@Dominic_Albans when you say column name changes do you mean the columns given in the above workflow in the text input change? Can you provide a sample input file for better understanding?

[Dominic_Albans]

The Colum name is derived from a formula "[Name]+':'+[Date]". As new data, for a new date, is expected everyday; how can I create a dynamic formula which calculate the variance between the last 2 date values. 

Input File:

Date	Identifier	Cost	Revenue
09/08/2024	ABCD	5	10
12/08/2024	ABCD	10	15
13/08/2024	ABCD	15	20
14/08/2024	ABCD	20	50

[binuacs]

@Dominic_Albans The given formula is dynamic, when you say variance are you expecting a new column called variance? can you provide the expected output?

[Dominic_Albans]

Sure:

Identifier	Cost:09/08/2024	Cost:12/08/2024	Cost:13/08/2024	Revenue:09/08/2024	Revenue:12/08/2024	Revenue:13/08/2024	Daily Variance: Cost
ABCD	5	10	15	10	15	20	5

Note the new column "Daily Variance: Cost" on the far right. Which is Cost:13/08/2024 - Cost:12/08/2024.

I need the formula and resultant new Colum, to dynamically account for changes in the Header due to a new date. For example, tommorow it should be: Cost:14/08/2024 - Cost:13/08/2024.

[binuacs]

are you calculating the variance only for the last two dates?

[Dominic_Albans]

Yes

[binuacs · Accepted solution]

@Dominic_Albans you can can calculate the variance separate and append with the final output