Calculate different rows from separate columns

[Nyx]

Hi,

I need a measure than can take a column and add or minus another column.

The columns will always have the same number of rows but will be from different dated rows.

I have this:

Jan-22	1
Feb-22	2
Mar-22	3
Apr-22	4
May-22	5
Jun-22	6
Jul-22		7
Aug-22		8
Sep-22		9
Oct-22		10
Nov-22		11
Dec-22		12

I want to do:

1-7=-6

2-8=-6

etc.

[v-yangliu-msft · Accepted solution]

Hi  @Nyx ,

Here are the steps you can follow：

1. Create calculated table.

Date =
CALENDAR(
    DATE(
        2022,1,1),
    DATE(
        2022,12,31))

2. Create calculated column.

Column =

var _table1=
SUMMARIZE(
    'Table1',
    'Table1'[Date],
    "Index1"
    MAXX(FILTER(ALL('Date'),
FORMAT(
   'Date'[Date],"mmm")=LEFT('Table1'[Date],3)),MONTH('Date'[Date])))
var _table2=
ADDCOLUMNS(
    _table1,"Index2",IF([Index1]<=6,
    MAXX(FILTER(_table1,[Index1]=EARLIER([Index1])+6),[Index1]),
     MAXX(FILTER(_table1,[Index1]=EARLIER([Index1])-6),[Index1])    
    ))
return
MINX(FILTER(_table2,[Date]=EARLIER(Table1[Date])),[Index1])
-
MINX(FILTER(_table2,[Date]=EARLIER(Table1[Date])),[Index2])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Nyx]

I cna split these into two table expressions easily, but cannot find a way to index them so that the first

populated row of column 2 gets subtracted by the first populated row of column 1. It wants to use the Date column as the index or filter to line up the equation.