Better Year Over Year Change | Microsoft Fabric Community

New Member

3 years ago

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!