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

[utnvols1]

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