thank you so much for your helpful hint.
I would need a further refining: instead of "previous_value" I need a "value at a specific date", where the specific date is a parameter from a list of available dates in the past.
Moreover I need a date projection: I mean, whenever value is not available because it is still not reported, i need to project the "value at a specific date", where the specific date is a parameter from a list of available dates in the past.
I will try to be more explicit with an example, as follows:
I have values up to January 2018; I need to project values in dates after January 2018; my idea is to use the value available in January 2018 (or even before) as a starting point to project values in February 2018 and so on, until a specified end date;
Finally these projected values should be subject to percentage increase/decrease via parameter.
I hope this example helps to explain.
We are almost there
Conceptually you are right; the problem is that I have a data-source with values until let's say June 2018; after this date I have zeros as values.
Now I need a formula able to pick a value from any date until June 2018 and copy it from July 2018 to a future date I would like to choose.
So I need two date parameter: the date from which I pick the value to be copied in the future and the date until I want copy this value.
I updated the data-source including DATES with empty values. Now I have a table similar to the following:
1 B C D 2 Date Profit Profit 2 3 Mar-17 12,123 12,123 4 Apr-17 34,432 34,432 5 May-17 34,432 34,432 6 Jun-17 12,332 12,332 7 Jul-17 34,342 34,342 8 Aug-17 65,656 65,656 9 Sep-17 - 37,443 10 Oct-17 - 45,814
"Profit" column is a calculated field, with aggregated values.
"Profit 2" column is another calculated field, which takes the corresponding "Profit" value when it is different of zero, otherwise the moving average of the previous three values of the column ""Profit 2".
Excel formula is like this (referring to yellow cell): =IF(C9<>0,C9,AVERAGE(D6:D8)).
Do you know how to transpose this kind of formula in a calculated field in Tableau? The best should be to get moving average as parametric (i.e. deciding how many previous values to consider).
Thank you so much!