Use column value from date hierarchy

[smsat]

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

[v-yuta-msft]

@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.

[smsat]

Hi @v-yuta-msft , just wondering if the info I provided below helped at all?

Would really appreciate your help if you can shed any light on this.

Thanks

[smsat]

Thanks for the reply @v-yuta-msft.  I'll try and explain a bit more thoroughly.  Below is an image of all data.  There is a line entry for every day in the last 6 years, which shows 

EOD GP = the cululative Gross Profit (GP) figure for all of that month, as at 2345 on that day, as supplied by our accounting system automatically into a file.  ie on Feb 11, we'd made £254,634 of Gross Profit for the month so far

Last month GP = this is the cumulative Gross Profit figure for the PREVIOUS MONTH, as supplied by accounting system into the file.  We include this because after last day of the month the GP figure still fluctuates for the following couple of weeks or so as final invoices and credits come in.  So on 11 Feb it shows the gross profit for all of January 

GP EOM = Cumulative final Gross Profit figure for this month.  This looks to the last day of next month and gets the figure from "Last month GP" column.  Getting this data now means we know today's GP value (EOD GP) and what this month will end up on (GP EOM).  So we can go on to calculate how much more GP was made between today and the final figure for the month ('Addtl GP to EOM').  Logic is: 

GP EOM = LOOKUPVALUE ('Advan GP data'[Last month GP],  'Advan GP data'[Date],EOMONTH('Advan GP data'[Date],1) )

Addtl GP to EOM = As described above, this shows how much more GP was made between today and the final figure for the month.  Logic:  AVERAGE('Advan GP data'[GP EOM]) - AVERAGE('Advan GP data'[EOD GP])

% inc to EOM is just the % of addtl GP generated between today and the final figure for the month.  

% inc to EOM = VAR __BASELINE_VALUE = AVERAGE('Advan GP data'[EOD GP])

VAR __VALUE_TO_COMPARE = [Addtl GP to EOM]

RETURN

    IF(  NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE, __BASELINE_VALUE ))

I've then created a table with date hierachy just showing month and day.  This summarises the 6 years of data so we end up with just 1 entry for each of the 365 days of the year. i.e. as we can see below Feb 11th the average across the 6 years is that 

- end of day GP is £200,425

- an additional £238,432 of GP will be made between Feb 11th the end of the month (as measured at the end of March)

What I'm stuck on is that I want to display this data in an additional way

- using today's date, pull out the EOD GP figure for last night, and then add on the AVERAGE figure for the 6 years (as seen below in 'addtl GP to EOM'.  This will give an estimated figure for what the month's GP will end on (as measured to end of next month).

Does that make sense?