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-22 ...
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

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