Better Year Over Year Change
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!
1 Reply
This is great, and I believe related to an issue I am currently having. I am trying to build this matrix
Current Month: Mar-23 2022 Prev Month Actual Budget 1 Budget 2 Budget 3 Total Sales 1150 2450 2600 2500 2300 2800 Total Expense 175 180 175 175 175 200 Year over Year Growth 150 450 1450 1350 1150 1650 Growth % 13% 23% 126% 117% 100% 143% Margin % 85% 93% 93% 93% 92% 93% Using this Data
Date Version Total Sales Total Expense 2/1/2021 Product A Actual 1000 100 2/1/2022 Product A Actual 2000 150 3/1/2021 Product A Actual 1000 100 3/1/2022 Product A Actual 1150 175 2/1/2023 Product A Budget 1 2100 175 2/1/2023 Product A Budget 2 2200 175 2/1/2023 Product A Budget 3 2400 200 3/1/2023 Product A Budget 1 2500 175 3/1/2023 Product A Budget 2 2300 175 3/1/2023 Product A Budget 3 2800 200 2/1/2023 Product A Actual 2450 180 3/1/2023 Product A Actual 2600 175 2/1/2021 Product B Actual 300 60 2/1/2022 Product B Actual 500 90 3/1/2021 Product B Actual 310 60 3/1/2022 Product B Actual 530 105 2/1/2023 Product B Budget 1 800 105 2/1/2023 Product B Budget 2 850 105 2/1/2023 Product B Budget 3 950 120 3/1/2023 Product B Budget 1 950 105 3/1/2023 Product B Budget 2 925 105 3/1/2023 Product B Budget 3 975 120 2/1/2023 Product B Actual 980 108 3/1/2023 Product B Actual 1000 105 The issue I am having is with year over year growth. I am currently using this measurement to try and calculate Previous year sales, but it only populates for the 'Actual' column.
PY Sales = CALCULATE(ROUND(SUM('DataTable'[Total Sales]),2),FILTER(ALL('Versions'[Version]),'Versions'[Version] = "Actual"),DATEADD('Date Table'[Date],-1,Year))
I have tried using different filter methods, but nothing I have tried has worked. This is probably easier than I am making it out to be, so I figured I would ask!
Thank you in advance for assistance!
JC
