Trying to get the last value into a column

[benduke99]

Hello, 

I am currently trying to manipulate some data I pull from FRED. 

The data looks like this, it is a very large data source so this is a small snippet, there are even more Index Names: 

Date	Year	MonthName	Index Name	Value
10/31/2023	2023	October	Global Price Soft Logs	208
9/30/2023	2023	September	Global Price Energy	202.9
8/31/2023	2023	October	Global Price WTI Crude	85.47
7/31/2023	2023	November	Global Price Tin	24501
6/30/2023	2023	December	Global Price Wool Coarse	835.9
5/31/2023	2023	January	Global Price Wool Fine	894.1
4/30/2023	2023	February	Global Price Industrial Materials	157.6
3/31/2023	2023	March	Global Price Nickel	18264
2/28/2023	2023	April	Global Price Cotton	95.69
1/31/2023	2023	May	Global Price Metal	180.2
12/31/2022	2022	June	Deep Sea Freight	410.1
11/30/2022	2022	July	Global Price Rubber	73.2
10/31/2022	2022	August	Global Price Aluminum	2194
9/30/2022	2022	September	Global Price Copper	7941
8/31/2022	2022	October	Global Price All Commodities	167.8
7/31/2022	2022	November	Wood Office Furn Mfg	270.7
6/30/2022	2022	December	General Freight Trucking	172.5
5/31/2022	2022	January	Wood Bedroom Furn Not Custom Mfg	361.5
4/30/2022	2022	February	Rail Freight	230.9
3/31/2022	2022	March	Construction Employees	8033
2/28/2022	2022	April	Canadian Dollar to U.S. Dollar Spot Exchange Rate	1.388

The data goes all the way back to when FRED started reporting on all these different indicis, commodities, prices, etc. 

What I want to do is add a column that has the Value from the previous month for the specific index, commodity, etc. 

Seems like it would be so simple but I have been tooling around with this and cannot find a solution (I am really new to DAX). 

At the end of the excercie I want to get to the % change from month to month but being in a table is important to my stakeholder, while I recognize a measure can probably do this, I need to be able to provide the table. 

Ideally the output would look like this:

Date	Year	MonthName	Index Name	Value	Last Months Value	%Change
10/31/2023	2023	October	Global Price Soft Logs	208	XXX	XXX
9/30/2023	2023	September	Global Price Energy	202.9	XXX	XXX
8/31/2023	2023	October	Global Price WTI Crude	85.47	XXX	XXX
7/31/2023	2023	November	Global Price Tin	24501	XXX	XXX

[FreemanZ · Accepted solution]

hi @benduke99 ,

not sure if i fully get you, supposing you have a data table like:

Date	Index	Value
11/1/2022	a	1
12/1/2022	a	1
12/2/2022	a	1
1/1/2023	a	3
2/1/2023	a	4
11/1/2022	b	4
12/1/2022	b	3
1/1/2023	b	2
2/1/2023	b	1

try to

1) add two calculated columns like:

Month = FORMAT([Date], "YY/MM")

MonthSN = YEAR([Date])*12+MONTH(data[Date]) 

2) plot a table visual with index and month columns and measures like:

ValueSum = SUM(data[Value])

MonthlyChange = 
VAR _pre = 
CALCULATE(
    [ValueSum], 
    data[MonthSN] = MAX(data[MonthSN]) - 1,
    ALL(data[Month]) 
)
VAR _result = [ValueSum] - _pre
RETURN _result

MonthlyChange% = DIVIDE([MonthlyChange], [ValueSum])

it worked like:

[benduke99]

This is exaclty what I was looking for!

Appreciate your help!