How do I remove a year that doesn't have data in power BI while calculating YoY growth

[Sofia_A]

Growth will be calculated from 2020 to 2022 as there was no sales in 2021

[FreemanZ · Accepted solution]

hi @Sofia_A ,

Not sure if i fully get you. Supposing you have a data table like:

you may try:
1) create a dates table like:

dates = ADDCOLUMNS( CALENDARAUTO(), "Year", YEAR([date]) )

2) relate the dates[date] with data[date] with 1:M relationship.
3) try to plot at table visual with dates[Year] column and a measure like:

AmtPY = 
VAR _lastrecordday =
MAXX(
    FILTER(
        ALL(data),
        data[date]<MIN(dates[date])
    ),
data[Date]
)
VAR _result =
SUMX(
    FILTER(
        ALL(data),
        YEAR(data[date]) = YEAR(_lastrecordday)
    ),
    data[Amt]
)
RETURN COALESCE(_result, 0)

it worked like: