Better Year Over Year Change 11-04-2022 08:18 AM - last edited 11-21-2022 08:55 AM

[Greg_Deckler]

Continuing with exploring alternatives to Power BI's default quick measures that don't involve the CALCULATE function, such as Better Running Total, Better Average per Category, Better Weighted Average per Category, Better Filtered Value, Better Sales from New Customers, and Year to Date Total. This one tackle Year Over Year Change.

Power BI's Year Over Year Change quick measure returns something like this:

Value YoY% = 
  VAR __PREV_YEAR = CALCULATE(SUM('Table'[Value]), DATEADD('Dates'[Date], -1, YEAR))
RETURN
  DIVIDE(SUM('Table'[Value]) - __PREV_YEAR, __PREV_YEAR)

or this:

Value YoY% 2 = 
IF(
  ISFILTERED('Dates'[Date]),
  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('Dates'[Date].[Date], -1, YEAR))
RETURN
  DIVIDE(SUM('Table'[Value]) - __PREV_YEAR, __PREV_YEAR)
)

Which may seem great until you try to use it with fiscal calendars and such. A better way:

Better Year Over Year Change = 
    VAR __Year = MAX('Table'[Year])
    VAR __Curr = SUMX(FILTER(ALL('Table'),[Year] = __Year),[Value])
    VAR __Prev = SUMX(FILTER(ALL('Table'),[Year] = __Year - 1),[Value])
RETURN
    DIVIDE(__Curr - __Prev, __Prev, 0)

Watch the video!

eyJrIjoiYWZlNDIzNjctMGUxZi00ZTU5LWI4MDgtYjI0MmRiYzQ4YjU3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9