Use column value from date hierarchy

smsat，

---

smsat wrote:

I have raw data going back 6 years which is 

1. date

2. Gross Profit (GP) figure at end of that day

3. Last month's cumulative GP at this date [this is because the GP figure for previous month changes during the next month due to credits/extra invoicing, so useful to know what it is on this date.  e.g. for month end December, the most accurate GP month end figure is looking at December's figure at the end of January, which is what this column shows]

e.g. 

11 Feb 2019 £644,000  £714,000

 

I've managed to achieve what I need, which is to work out for each date, how much extra GP was added between that date and the end of month, and then summarise this in a date hierarchy by month and date over the 6 years, e.g. 

 

Month   Day  Avg GP on this date    Avg addtl GP added by month end

February  11        £200,425                               £238,432

 

What I now need to do is to use those 2 pieces of data together, i.e. I can get the current GP (i.e. yesterday's), in the example above: £644,000

How am I then able to get the average "Avg addtl GP added by month end" figure from an average date, e.g . the average of all the February 11th data, as the table has summarised above?

 

i.e. so I would present the data as 

Current GP: £644,000

Avg addtl GP added by month end: £238,432

Estimated month end total: £882,432

---

Could you share some sample data and clarify more details about the logic? For example, how do you get £200,425£238,432, Avg addtl GP from Current GP: £644,000?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.