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

     

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.