Change the growth return to return 100% when there are no values in previous year

[Anonymous]

Hi all,

I have created a new calculation using the "New Quick Measure"  feature in power bi. The issue is, when I have a new item for example then it does not calculate 100% growth, however when the item was existent in the previous period but not this period then it shows -100% , and this is understantable. So pretty much when the Change for last year returns a blank value then the value should be 100% if seem on last year, but if orevious year is blank

For example:

Year                  Task                          Previous_year                    This_year           Compared to last (ABS)                     Change from last year

2019                  A                                       0.27                                        0.89                                   0.62                                                         229.63%

2019                 B                                        0.23                                        0.05                                  -0.18                                                       -78.26%

2019                NEW                                  -                                              0.63                                                                                          ( should be  100%)100%)

2019                C                                          0.45                                         -                                                                                                                -100%

2018                A                                          0.27                                      0.27                                                                                                    ( not here )

2018                B                                          0.23                                       0.23                                                                                                    ( not here )

2018                C                                          0.45                                       0.45                                                                                                    ( not here  )

Please see below my DAX formula for this calculation so far.

Change from last year =
IF(
 ISFILTERED('Date22'[Date]),
 ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
 VAR __PREV_YEAR =
  CALCULATE(
   SUM('task_ratings_onet_long_trial_DWA'[This Year]),
   DATEADD('Date22'[Date].[Date], -1, YEAR)
  )
 RETURN
  DIVIDE(
   SUM('task_ratings_onet_long_trial_DWA'[This Year])
    - __PREV_YEAR,
   __PREV_YEAR
  )
)

Thanks a lot.

UPDATE:

Pretty much I need to find a away if the task is new to flag 100% or even a new variable called new for task that appear this year not previous years.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try to add IF condition like below.

Change from last year =
IF (
    ISFILTERED ( 'Date22'[Date] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __PREV_YEAR =
        CALCULATE (
            SUM ( 'task_ratings_onet_long_trial_DWA'[This Year] ),
            DATEADD ( 'Date22'[Date].[Date], -1, YEAR )
        )
    RETURN
        IF (
            __PREV_YEAR = BLANK (),
            1,
            DIVIDE (
                SUM ( 'task_ratings_onet_long_trial_DWA'[This Year] ) - __PREV_YEAR,
                __PREV_YEAR
            )
        )
)

Regards,

[Anonymous]

Hi Cherie Chen,

Thank you so much for helping out, I think this code solved a half of the issue, I got the 100% in the most recent year but also got 100% now in all previous blanks.

For example

year          task                minutes             growth

1                 A                           12                           50%                   

1                 B                            20                          100%  ( which is what i was looking for)

2                 A                            8                             100% ( here and all below is now 100%)

Te new code proposed by you say if previous year is blank repalace by one, but how could i keep all previous years observations blank?

Tks a lot