Difference From Previous Week With Filters

[squiddly]

I've seen similar topics but I think my case goes a bit further.  I have a database with about 3 million rows and growing of data.  There's a lot more to this data, but the relevent (and fuzzed) columns are here:

 

Columns A thru D are in my data.  Column E shows what I'd like to show or calculate with Power BI.  I need to apply this logic to the calculation:

LastWeekComp = 	Lookup where Location = Location
		Lookup where IAVA = IAVA
		Lookup where Date = Date -7 days
		Return Compliant

And so the calculation of that final column would simply be

Difference From Previous Week = Compliant - LastWeekComp

I've seen lots of different suggestions for how to tackle this, and I've tried going down each one.  I just end up confusing myself.  Can any of you smart smart people help me down the right path?  Thank you so much for any insights!

[Phil_Seamark · Accepted solution]

Hi @squiddly

Please try adding this calculated column to your table.  Just replace the text 'Table1' with your own table name.  Remember to format the column as approprate.  

Column = 
VAR LastWeekComp = 
    SUMX(
        FILTER(
            'Table1',
            'Table1'[Location] = EARLIER('Table1'[Location]) &&
            'Table1'[Date] = EARLIER('Table1'[Date])-7  &&
            'Table1'[CVE] = EARLIER('Table1'[CVE])
            )
            ,'Table1'[Compliant])    
VAR Compliant = 'Table1'[Compliant]
RETURN  Compliant - LastWeekComp

[weixiangzhang]

Thank you!! It works marvelously, now I can use slicer on this thing. 

[squiddly]

Phil, you are so awesome.  I took this home to work on over a 3-day weekend and I was getting nowhere.

That code worked perfectly for my sample.  Will be implementing it this week at work.  Thanks so much!

[parry2k]

First foremost you need calendar table in your data model for any time based calculation, either it is previous month/week/year etc. You need to start with that then go from there. There are many posts on how to create calendar table in PowerBI which can get started you on that.