cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Help with estimating

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.

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
4 REPLIES 4
Community Support

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg

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

Jo

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors