Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

 

Thanks in advance

 

Jo

 

 

CompanyFinancial YearRevenue
AFY17/1810
AFY18/1920
AFY19/2030
BFY17/1815
BFY18/1932
BFY19/2045
CFY17/1820
CFY18/1940
Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    So what is your formula for calculating estimated revenue? Average of the previous years? maximum, minimum?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.