Forum Discussion
New Column Change by Year
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
Hi Anonymous
First calculate previous year's numberPrevious 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)
4 Replies
Hi,
Modify your measure to
=IFERROR(your_measure,1)
Hope this helps.
Hi Anonymous
First calculate previous year's numberPrevious 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)
