Omitting Value of Zero from Calculation

[tarmogolf]

I have the following measure, which works *almost* perfectly for what I need:

Traffic Cnt YoY% =

VAR __PREV_YEAR = CALCULATE([Traffic Cnt Trend], DATEADD('Date'[Date], -1, YEAR))

RETURN

    DIVIDE([Traffic Cnt Trend] - __PREV_YEAR, __PREV_YEAR)

The issue is, though, that some of the locations do not have Traffic Cnt for both years. Management's request is that they only want to see the YoY comparison for locations where Traffic Cnt if not zero in both years. I've tried filtering out <>0 values in __PREV_YEAR but am not having success.

[johnt75]

You could try something like

Traffic Cnt YOY% =
var currentLocations = CALCULATETABLE( VALUES('Table'[location]), [Traffic Count] > 0 )
var lastYearLocations = CALCULATETABLE( VALUES('Table'[location]), [Traffic Count] > 0, DATEADD('Date'[Date], -1, YEAR) )
var result = CALCULATE( 
   var _PREV_YEAR = CALCULATE([Traffic Cnt Trend], DATEADD('Date'[Date], -1, YEAR))
   return DIVIDE( [Traffic Cnt Trend] - _PREV_YEAR, PREV_YEAR),
TREATAS( INTERSECT( currentLocations, lastYearLocations), 'Table'[location] )
)
return result

[tarmogolf]

Hi there, thanks for replying. I've tried to implement this solution and it says "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

I was receiving this error previously when messing around with solutions I could come up with - I think it's mad that I'm trying to do the "[Traffic Cnt] >0" filter.

[johnt75]

I think the overall principle of using the intersection between this year and last year will still work, we just need to get those values slightly differently. Try replacing the lines for this year and last year with

var currentYearSummaryTable = ADDCOLUMNS( SUMMARIZE('Table', 'Table'[location]), "@traffic", CALCULATE([Traffic count]) )
var lastYearSummaryTable = ADDCOLUMNS( SUMMARIZE('Table', 'Table'[location]), "@traffic", CALCULATE([Traffic count], DATEADD('Date'[Date], -1, YEAR)) )
var currentLocations = SELECTCOLUMNS( FILTER( currentYearSummaryTable, [@traffic] > 0), "location", [location])
var lastYearLocations = SELECTCOLUMNS( FILTER( lastYearSummaryTable, [@traffic] > 0), "location", [location])

[amitchandak]

@tarmogolf ,

Traffic Cnt YoY% =
VAR __PREV_YEAR = CALCULATE([Traffic Cnt Trend], DATEADD('Date'[Date], -1, YEAR))
RETURN
if([Traffic Cnt Trend] <> 0 && __PREV_YEAR <> 0,  DIVIDE([Traffic Cnt Trend] - __PREV_YEAR, __PREV_YEAR), blank())

[tarmogolf]

Thanks for replying, I appreciate your help here. When I try to implement your solution it still factors in locations where Traffic Cnt Prev Year = 0 when it's aggregated up into a higher level in the hierarchy.