Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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