Help with estimating

[Anonymous]

Hello

I have a simple file which is based on a regulatory returns a company submits to a regulatory body.  The company submits this file at the end of each financial year and the revenue amongst other things is then used to determine regulatory fees.  If a company does not submit on time then their fees are based on their previous year's revenue.  I wanted to be able to calculate the company's (c in the below example) estimated revenue for  FY19/20 as they have yet to submit.  I also wanted to calculate the whole revenue for all companies for FY19/20 including the estimated values however I am stuck on how to start both points as it means trying to create a value for company c for FY19/20 when it doesn't exist.  I'm sure there must be a solution and I hope you can help.

Thanks in advance

Jo

Company	Financial Year	Revenue
A	FY17/18	10
A	FY18/19	20
A	FY19/20	30
B	FY17/18	15
B	FY18/19	32
B	FY19/20	45
C	FY17/18	20
C	FY18/19	40

[V-pazhen-msft]

@Anonymous 

I create a sample with blank revenue for FY19/20. You need to create an Index Column to get the revenue from previous row (previews FY) as Est Revenue.

BTW, I am not sure if this is your requirement, but it is not possible to add a row from no where in to the current table, you need to have the Company or FY in the first place.

Est Revenue = IF('Table'[Revenue]=BLANK(),CALCULATE(SUM('Table'[Revenue]),FILTER('Table',[Index]=EARLIER('Table'[Index])-1),ALLEXCEPT('Table','Table'[Company])))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

So what is your formula for calculating estimated revenue? Average of the previous years? maximum, minimum?

[Anonymous]

Hi Greg

Apologies for not mentioning in the original post.  It's just basically the revenue from the previous financial year.

Thanks for your help

Jo

[Greg_Deckler]

I would use an Enter Data query to enter your estimates. Then you could use UNION to union the two tables together and perform operations on them.