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) colu...
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

     

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.