New Column Change by Year

[Anonymous]

Hi All,

I have sucessfully create a measure that calculate year by year change. However, due to peculiiarities of the data i believe I need to create a column with the numbers. I have no idea how to do it to be honest, any help will be greatly appreciatted. There are many other years in the data, asshown in the Year 2010, Occupation D is new threfore it must be 100%. Thanks Again.

Year                  Occupation                  Number                 GROWTH

2010                      A                                         10                                 100%

2010                      B                                         10                                  100%

2010                      C                                         9                                       50%

2010                      D                                          5                                    100% ( not in the previous period therefore must be 100%)

2009                     A                                           5                                   

2009                     B                                           5

2009                    C                                            6

[Anonymous · Accepted solution]

Hi @Anonymous 
First calculate previous year's number

Previous Number = 
CALCULATE (
    MAX ( 'Table1'[Number] ),
    FILTER (
        'Table1',
        YEAR ( 'Table1'[Year] )
            = YEAR ( EARLIER ( 'Table1'[Year] ) ) - 1
            && [Occuption] = EARLIER ( Table1[Occuption] )
    )
)

Then calculate growth

Growth = IF(DIVIDE(Table1[Number],Table1[Previous Number], 1) = 1,
1,
DIVIDE(Table1[Number],Table1[Previous Number], 1) - 1)

[Anonymous]

Hi Aclerk,

Thanks for providing an answer.

Is you response formula to built a measure or a new column?

Thanks,

Ian

[Anonymous]

Hi,

I have used 2 new columns in my solution.

Ta!

[Ashish_Mathur]

Hi,

Modify your measure to

=IFERROR(your_measure,1)

Hope this helps.