Forum Discussion

squiddly's avatar
squiddly
Advocate I
8 years ago
Solved

Difference From Previous Week With Filters

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!

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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

     

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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

     

    • squiddly's avatar
      squiddly
      Advocate I
      8 years ago

      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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    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.