Compare table of values to first value

[Anonymous]

I have a table compomised of two different forecasts, and the month lag they were submitted. 
I select the month they are for, and it will show me the past forecasts.

I want to compare the first value of each column to each preceding value.

Additionally, I want to to do the same, but instead of the first forecast value, I would like the key comparison value to be a new column/measure (In my case, actuals).

Due to the way I have the data transformed, for each month there are duplicates for each actual value, depending on how many versions of each forecast there are.

Sample PBI report with a few examples of what I hope to show.

Sample File 

Sample table of the data.

SKU	Month	Forecast Creation	Region	Type	Actuals	Forecast Type	Forecast
30069	Jan-24	Dec-23	England	Franchise	200	Original	100
30069	Jan-24	Dec-23	England	Equity	2000	Original	1000
30069	Jan-24	Dec-23	Ireland	Franchise	75	Original	2000
30069	Jan-24	Dec-23	Ireland	Equity	750	Original	2100
40069	Jan-24	Dec-23	England	Franchise	1000	Original	500
40069	Jan-24	Dec-23	England	Equity	1000	Original	1700
40069	Jan-24	Dec-23	Ireland	Franchise	2100	Original	450
40069	Jan-24	Dec-23	Ireland	Equity	750	Original	500
50069	Jan-24	Dec-23	England	Franchise	299	Original	450
50069	Jan-24	Dec-23	England	Equity	250	Original	200
50069	Jan-24	Dec-23	Ireland	Franchise	600	Original	450
50069	Jan-24	Dec-23	Ireland	Equity	750	Original	900
30069	Jan-24	Dec-23	England	Franchise	200	Updated	110
30069	Jan-24	Dec-23	England	Equity	2000	Updated	1100
30069	Jan-24	Dec-23	Ireland	Franchise	75	Updated	2200
30069	Jan-24	Dec-23	Ireland	Equity	750	Updated	2310
40069	Jan-24	Dec-23	England	Franchise	1000	Updated	550
40069	Jan-24	Dec-23	England	Equity	1000	Updated	1870
40069	Jan-24	Dec-23	Ireland	Franchise	2100	Updated	495
40069	Jan-24	Dec-23	Ireland	Equity	750	Updated	550
50069	Jan-24	Dec-23	England	Franchise	299	Updated	495
50069	Jan-24	Dec-23	England	Equity	250	Updated	220
50069	Jan-24	Dec-23	Ireland	Franchise	600	Updated	495
50069	Jan-24	Dec-23	Ireland	Equity	750	Updated	990
30069	Jan-24	Nov-23	England	Franchise	200	Original	50
30069	Jan-24	Nov-23	England	Equity	2000	Original	500
30069	Jan-24	Nov-23	Ireland	Franchise	75	Original	1000
30069	Jan-24	Nov-23	Ireland	Equity	750	Original	1050
40069	Jan-24	Nov-23	England	Franchise	1000	Original	250
40069	Jan-24	Nov-23	England	Equity	1000	Original	850
40069	Jan-24	Nov-23	Ireland	Franchise	2100	Original	225
40069	Jan-24	Nov-23	Ireland	Equity	750	Original	250
50069	Jan-24	Nov-23	England	Franchise	299	Original	225
50069	Jan-24	Nov-23	England	Equity	250	Original	100
50069	Jan-24	Nov-23	Ireland	Franchise	600	Original	225
50069	Jan-24	Nov-23	Ireland	Equity	750	Original	450
30069	Jan-24	Nov-23	England	Franchise	200	Updated	55
30069	Jan-24	Nov-23	England	Equity	2000	Updated	550
30069	Jan-24	Nov-23	Ireland	Franchise	75	Updated	1100
30069	Jan-24	Nov-23	Ireland	Equity	750	Updated	1155
40069	Jan-24	Nov-23	England	Franchise	1000	Updated	275
40069	Jan-24	Nov-23	England	Equity	1000	Updated	935
40069	Jan-24	Nov-23	Ireland	Franchise	2100	Updated	247.5
40069	Jan-24	Nov-23	Ireland	Equity	750	Updated	275
50069	Jan-24	Nov-23	England	Franchise	299	Updated	247.5
50069	Jan-24	Nov-23	England	Equity	250	Updated	110
50069	Jan-24	Nov-23	Ireland	Franchise	600	Updated	247.5
50069	Jan-24	Nov-23	Ireland	Equity	750	Updated	495

[amitchandak]

@Anonymous , You can use offset or the new previous Table level calculation 

example, Please include all the column in visual

Last Category Brand = CALCULATE([net], OFFSET(-1, ALLSELECTED('Item'[Brand],'Item'[Category]), ORDERBY('Item'[Category],asc),KEEP,PARTITIONBY('Item'[Brand])))
Next Category Brand = CALCULATE([net], OFFSET(1, ALLSELECTED('Item'[Brand],'Item'[Category]), ORDERBY('Item'[Category],asc),KEEP,PARTITIONBY('Item'[Brand])))

PARTITIONBY is optional 

In case you have column across tables

Last Category Brand = CALCULATE([net], OFFSET(-1, Summarize(ALLSELECTED(Sales) , Geo[City], 'Item'[Brand],'Item'[Category]), ORDERBY('Item'[Category],asc)))

Continue to explore Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Power BI Update: Visual calculations (preview)
https://powerbi.microsoft.com/en-us/blog/visual-calculations-preview/

[Anonymous]

Hi amitchandak, 

Thanks for replying and sharing the video.

I've already been able to work out offset for previous values / next value. 
The help I need is to offset, but always point to the first rows value, rather than the previous rows value.

In my example above, I want the offset to always look at the row M-01. 
The formula will need to be " M-01 - M-## " With the ## increasing, but always compared to M-01.

I was able to get it to work without column across table using filter. However, it doesnt work when using column across table. - In my sample file, I have called this measure.

Thanks