Forum Discussion
Solved
Calculating month-over-month while excluding blanks
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!
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!
Hello Lanabanana
Give this a try
Month over month = VAR CurrMonth = SUM ( 'Product'[VALUE] ) RETURN IF ( NOT ISBLANK ( CurrMonth ), DIVIDE ( CurrMonth, [PREV_MONTH] ) - 1 )
3 Replies
Hello Lanabanana
Give this a try
Month over month = VAR CurrMonth = SUM ( 'Product'[VALUE] ) RETURN IF ( NOT ISBLANK ( CurrMonth ), DIVIDE ( CurrMonth, [PREV_MONTH] ) - 1 )- 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! - That did the trick! Amazing - thank you :)
