Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mibu
Employee
Employee

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

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
1 ACCEPTED SOLUTION
Mikelytics
Resident Rockstar
Resident Rockstar

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

5 REPLIES 5
mibu
Employee
Employee

@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

Hi @mibu: , 

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

Best regards

Michael

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!
mangaus1111
Solution Sage
Solution Sage

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.

@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
Resident Rockstar
Resident Rockstar

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.