Forum Discussion

Nyx's avatar
Nyx
Frequent Visitor
3 years ago
Solved

Calculate different rows from separate columns

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-221 
Feb-222 
Mar-223 
Apr-224 
May-225 
Jun-226 
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.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Nyx's avatar
    Nyx
    Frequent Visitor
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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