Calculating month-over-month while excluding blanks

[Lanabanana]

Hi everyone,

I'm looking to exclude blank cells from a measure I've created.
I've got a formula where I'm calculating the month over month over a 15 year period for 27 products.
However, some of my products are introduced in the middle of the 15 year period. When I put them all in a visual, the products that start in the middle show -100% for all of the previous months.
The visual calculates the bottom N products and it always takes these products with the -100%
I've tried doing a simple filter that excludes the -100% but then when I filter for years that are prior to the middle of the period, the visual shows as empty.

Here are my measures:
PREV_MONTH = CALCULATE(SUM('Product '[VALUE]),DATEADD('Product'[Reference Period],-1,MONTH))

 Month over month = CALCULATE((DIVIDE(SUM('Product'[VALUE]),[PREV_MONTH])-1))


How do I insert a bit to exclude the calculating of blank values?

Thank you!

[jdbuchanan71 · Accepted solution]

Hello @Lanabanana 

Give this a try

Month over month =
VAR CurrMonth =
    SUM ( 'Product'[VALUE] )
RETURN
    IF ( NOT ISBLANK ( CurrMonth ), DIVIDE ( CurrMonth, [PREV_MONTH] ) - 1 )

[Lanabanana]

Hey ,

This formula has worked for the most part.
But when I look at the first month that the product is introduced, it still shows -100% and I can't include my visual with the largest decreases for that month.
Is there a workaround to this?


EDIT: I replaced Currmonth with [PREV_MONTH] in the brackets after NOT ISBLANK and no more -100% for the first month. All fixed!

Thanks!

[Lanabanana]

That did the trick! Amazing - thank you 🙂