Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.