Forum Discussion

dsandberg's avatar
dsandberg
Icon for Advocate I rankAdvocate I
9 years ago
Solved

Set value from existing column/row when adding new column to fact table.

I have a fact table which lists widgets, years, and an attribute of the widget in that year. For example: Widget_ID    Year    Attribute 1001              2005       1 1001              2006      ...
  • dsandberg's avatar
    dsandberg
    9 years ago

    Yes, this worked; I used the MAXX option because my table did have more columns. The column formula turned out to be:

     

    =MAXX(FILTER('Table',('Table'[Widget_ID] = EARLIEST('Table'[Widget_ID]) && 'Table'[Year] = EARLIER('Table'[Year])-1)),[Attribute])

LaurentCouartou's avatar
LaurentCouartou
Icon for Solution Supplier rankSolution Supplier
9 years ago

Is it what you were looking for?

CALCULATE( MAX('Table'[Attribute])
       ,'Table'[Year] = EARLIER('Table'[Year])- 1
       ,'Table[Widget_ID] = EARLIER('Table'[Widget_ID])
)

Note that, due to the way CALCULATE works, you may get unexpected results if your table has more columns than in your description.

 

You may prefer something like the following instead:

MAXX( FILTER('Table'
               ,'Table'[Year] = EARLIER('Table'[Year])- 1
               && 'Table[Widget_ID] = EARLIER('Table'[Widget_ID]
           )
          , [Attribute]
)
  • dsandberg's avatar
    dsandberg
    Icon for Advocate I rankAdvocate I
    9 years ago

    Yes, this worked; I used the MAXX option because my table did have more columns. The column formula turned out to be:

     

    =MAXX(FILTER('Table',('Table'[Widget_ID] = EARLIEST('Table'[Widget_ID]) && 'Table'[Year] = EARLIER('Table'[Year])-1)),[Attribute])