Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 15
1001 2007 12
1001 2008 1
What I'd like to do is create another column that has the value of the widget's previous year attribute, which should look like this:
Widget_ID Year Attribute PY Attribute
1001 2005 1 N/A
1001 2006 15 1
1001 2007 12 15
1001 2008 1 12
I've been playing around with CALCULATE and FILTER commands, but I feel very clumsy with DAX at this point. Here is the gist of what I've been trying:
PY Attribute = CALCULATE(MAX('Table'[Attribue]),'Table'[Year] = ('Table'[Year] - 1),'Table[Widget_ID] ='Table'[Widget_ID])
Any advice or tips would be appreciated.
Solved! Go to Solution.
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])
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] )
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])
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
38 | |
22 | |
18 | |
15 | |
13 |