Creating a new column showing change in values in power query

[Anonymous]

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!!

[v-frfei-msft · Accepted 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.

[Anonymous]

@v-frfei-msft 

This works - thanks for your help!