Measure is Always Returning Zero

I recently came across a very strange bug (well it wasn't a bug but seemed like a bug!) where a simple "+0" caused my measure to always return 0, even though I know it shouldn't be 0. So for example, I had a simple measure like the following:

Percent of Blue Sales = 
	VAR _BlueSales = 
    		CALCULATE(
        		[Total Sales], 
        		Products[ColorName] = "Blue"
     	)
	VAR _NonBlueSales = 
     	CALCULATE(
         	[Total Sales], 
         	Products[ColorName] <> "Blue"
     	)
RETURN
	DIVIDE ( _BlueSales, _NonBlueSales, 0 )

The objective was to replace blanks with 0 (The Phone Company row in the image above). So a simple modification should suffice:

Percent of Sales (+0) = 
	VAR _BlueSales = 
     	CALCULATE(
         	[Total Sales], 
         	Products[ColorName] = "Blue"
     	) + 0
	VAR _NonBlueSales = 
     	CALCULATE(
         	[Total Sales], 
         	Products[ColorName] <> "Blue"
     	) + 0
RETURN
	DIVIDE ( _BlueSales, _NonBlueSales, 0 )

Note: the "+ 0" is a quick way to return zero if blank.

But this changes all the numbers to 0!

You would think such a simple change wouldn't cause a game-breaking bug. It's one of those bugs that make you question the utility of DAX in the first place. Indeed, this may not even happen with your mode. However, there is more at play than meets the eye...

The cause

So, why is my measure always returning 0? I will save you the pain of explaining the debugging process and skip to the cause. But in short, the issue is two fold.

1. My report had some calculation groups applied to the page, which apply a LASTNONBLANK date filter to the filter context.

2. The date table extended past the last date in the fact table.

These two points are not issues in and of themselves. However, when combined together over a measure that never returns blank (the "+ 0"), will end up displaying the strange behavior described before.

Why this is happening

Essentially, when we introduce "+0" to the measure, LASTNONBLANK evaluates the measure AND "+0". Therefore, LASTNONBLANK will return the last row for the column you give it (because no rows will be blank - they will always have a number (hence the "+0").

The solution

Either use a different function for LASTNONBLANK, trim the date table to the last date in the fact table, or both. LASTNONBLANK is a relatively expensive function anyways, so replacing it with a simple filter of the last month will be best. Trimming the date table is risky because it alters the context of the entire report. If you are just starting off, this may be fine. But if you are working on a well established model this may not be the best option.