Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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])
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |