Forum Discussion
Creating a new column showing change in values in power query
Hi All,
I'm new to PowerBI - I'm finding it useful but I can still do things in Excel much faster so there's a long way to go yet!
I have a table with the following columns:
Date - An end of month date - 30 June 2019 to 30 June 2002
Name: Name of the institution - ~150 institutions
Loan amount: Showing the amount of the loan - $
I would like to create a new column in PowerQuery that shows the difference in loan amount from one year ago. The solutions on this forum talk about creating an index key and then writing a formula to return the current row -1. That will not work for me because I would need the index to work for both my Date and Name columns.
Does anybody know how to do this?
Thanks!!
Hi Anonymous ,
We can create a calculated column to work on it instead creating custom column in power query.
Difference = VAR lastyear = EDATE ( 'Table'[date], -12 ) VAR na = 'Table'[Name] VAR lastyearloan = CALCULATE ( SUM ( 'Table'[Loan amount] ), FILTER ( 'Table', 'Table'[date] = lastyear && 'Table'[Name] = na ) ) RETURN IF ( ISBLANK ( lastyearloan ), BLANK (), 'Table'[Loan amount] - lastyearloan )BTW, pbix as attached.
2 Replies
v-frfei-msftCommunity Support
Hi Anonymous ,
We can create a calculated column to work on it instead creating custom column in power query.
Difference = VAR lastyear = EDATE ( 'Table'[date], -12 ) VAR na = 'Table'[Name] VAR lastyearloan = CALCULATE ( SUM ( 'Table'[Loan amount] ), FILTER ( 'Table', 'Table'[date] = lastyear && 'Table'[Name] = na ) ) RETURN IF ( ISBLANK ( lastyearloan ), BLANK (), 'Table'[Loan amount] - lastyearloan )BTW, pbix as attached.