Year over year Unit Growth Measure which handles null and negative values

[mibu]

Hi Team,

I have a measure which determines year-over-year unit growth as follows:

UnitGrowth = ((SUM(TableName[Units])-CALCULATE(SUM(TableName[Units]),DATEADD(DateTable[Day],-365,DAY)))/CALCULATE(SUM(TableName[Units]),DATEADD(DateTable[Day],-365,DAY)))

I then create a table with the following columns:

Price Band / Units / Unit Growth

Sometimes, however, the prior-year-timeframe Units - within a Price Band - are negative (or null)...  which results in a Unit Growth calculation like this:

(8294-(-1))/(-1) = -829500%

Please advise how to make Power BI simply not show results such as these - where Unit Growth is determined with a negative number in the formula.

Thank you!

Very best,

mibu

[mibu]

@Mikelytics  Thanks so much for your reply!  I appreciate learning the variable feature from the solution you provided.  I can see it being very handy going forward.  🙂  Very best, mibu

[Mikelytics]

Hi @mibu: , 

Awsome and thank you for your feedback! 🙂 I am happy to help!

Best regards

Michael

[mangaus1111]

Hi @mibu ,

see my solution in the attached file.

https://1drv.ms/u/s!Aj45jbu0mDVJi1Pc5qgfG3xymY10?e=Y0x2tr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mibu]

@mangaus1111 Thank you for your reply!  I see you used variable as well.  I didn't get a chance to work it into my model but it looks very similar to @Mikelytics solution and imagine it would bear same good fruit.  Again, thank you for your feedback...  much appreciated!  Very best, mibu

[Mikelytics · Accepted solution]

Hi @mibu ,

I did two things

1. I simplified you measure using a variable

2. I implemented a rule which provides ablank result when the previous year value is smaller than zero

Please try it out

UnitGrowth = 

var PreviousYear_Value =
   CALCULATE(
      SUM(TableName[Units]),
      DATEADD(DateTable[Day],-365,DAY)
   )

RETURN

IF(
   PreviousYear_Value > 0,
   DIVIDE(
      SUM(TableName[Units]) - PreviousYear_Value,
      PreviousYear_Value
   )
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.