Year over Year calculation generating -100% when month exists in last year but not this year

[drew_darke]

Hi all,
I am doing some year over year calculations and came across an issue I can't seem to solve. I am using the built in YoY quick measure. What's happening here is its trying to do the calculation for december 2021 because december 2020 exists, however there is no data for december 2021 (nor is there a date in the dataset for december 2021).

How do I get rid of this -100.00%?

Thanks!

[drew_darke]

Here is the built in measure for reference

VALUE YoY% =

IF(

    ISFILTERED('TABLE'[REF_YEAR]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR = CALCULATE(SUM('TABLE'[VALUE]), DATEADD('TABLE'[REF_YEAR].[Date], -1, YEAR))

    RETURN

        DIVIDE(SUM('TABLE'[VALUE]) - __PREV_YEAR, __PREV_YEAR)

)

[AlexisOlson · Accepted solution]

Try checking for a blank sum:

VALUE YoY% =
IF (
    ISFILTERED ( 'TABLE'[REF_YEAR] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __PREV_YEAR =
        CALCULATE (
            SUM ( 'TABLE'[VALUE] ),
            DATEADD ( 'TABLE'[REF_YEAR].[Date], -1, YEAR )
        )
    VAR __CURR_YEAR = SUM ( 'TABLE'[VALUE] )
    RETURN
        IF (
            ISBLANK ( __CURR_YEAR ) || ISBLANK ( __PREV_YEAR ),
            BLANK (),
            DIVIDE ( __CURR_YEAR - __PREV_YEAR, __PREV_YEAR )
        )
)