Finding the latest trend in a dataset

[JamesCanale]

I have data that has many monthly records for each item. What I want to get is a new table with each item appearing once and the change between the current month and the prior month.

How can this be done best? This is how I would do it in excel. It's not as elegant (in excel) as I would like it.

MrExcelPlayground21.xlsx
	A	B	C
2	Item	Date	Thing
3	A	12/1/2023	50
4	A	1/1/2024	52
5	A	2/1/2024	54
6	A	3/1/2024	54
7	B	12/1/2023	400
8	B	1/1/2024	400
9	B	2/1/2024	400
10	B	3/1/2024	410
11	C	12/1/2023	100
12	C	1/1/2024	120
13	C	2/1/2024	120
14	C	3/1/2024	105
15
16	Item	Change in Last month
17	A	0
18	B	10
19	C	-15
Sheet4

Cell Formulas
Range	Formula
B17:B19	B17	=LET(a,FILTER($B$3:$C$14,$A$3:$A$14=A17),b,SORT(a,1,-1),c,TAKE(b,2),d,DROP(c,,1),e,TAKE(d,1)-TAKE(d,-1),e)

 

[JEC]

Hi again!

Add this measure:

M_Diff =
 CALCULATE(
    MAX(tbl_Projects[Thing]),
    tbl_Projects[Date] = MAX(tbl_Projects[Date])
  ) -
  CALCULATE(
        MAX(tbl_Projects[Thing]),
        PARALLELPERIOD(tbl_Projects[Date],-1,MONTH)
    )

Then create a table in your report again. Add the Item column and the measure above.

 

[JamesCanale]

This is gold again. Thanks. Have a great weekend.

 

[JEC]

You too! Cheers!