Forum Discussion

MargaretJames's avatar
MargaretJames
Regular Visitor
5 years ago
Solved

Calculating a week before value

Hi,    I'm trying to calculate the total week value in a table. I would like to subtract the week before value from the current week. I'm having an issue with my week before value.  I created a me...
  • Anonymous's avatar
    Anonymous
    5 years ago
    [Sum Of Cases] = SUM( CovidPerCountry[ConfirmedCovidCases] )

[7Day Rolling Sum] =
var LastVisibleDate = MAX( 'Date'[Date] )
var _7DayPeriod =
    DATESINPERIOD(
        'Date'[Date],
        LastVisibleDate,
        -7,
        DAY
    )
var Result = 
    CALCULATE(
        [Sum Of Cases],
        _7DayPeriod,
        // If your 'Date' table is marked
        // as a date table in the model,
        // this last line is unnecessary.
        ALL( 'Date' )
    )
RETURN
    Result

    This works but when you're nearing the beginning of time in your 'Date' table, for the first 6 days you'll get a sum over the existing days, of which there will not be exactly 7. You can either ignore this or you can change the logic so that the result is calculated only if _7DayPeriod contains exactly 7 days.

MargaretJames's avatar
MargaretJames
Regular Visitor
5 years ago

HI Anonymous. Thanks. Yes, I created a date table, you can see my file above. I'm just stuck on the -7 days part. 

Basically I need the number 7 days before the measure below. I'm unsure how to do that. 

CALCULATE (
    SUM( covidpercounty[ConfirmedCovidCases] ),
    FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 )
)
Anonymous's avatar
Anonymous
Not applicable
5 years ago
[Sum Of Cases] = SUM( CovidPerCountry[ConfirmedCovidCases] )

[7Day Rolling Sum] =
var LastVisibleDate = MAX( 'Date'[Date] )
var _7DayPeriod =
    DATESINPERIOD(
        'Date'[Date],
        LastVisibleDate,
        -7,
        DAY
    )
var Result = 
    CALCULATE(
        [Sum Of Cases],
        _7DayPeriod,
        // If your 'Date' table is marked
        // as a date table in the model,
        // this last line is unnecessary.
        ALL( 'Date' )
    )
RETURN
    Result

This works but when you're nearing the beginning of time in your 'Date' table, for the first 6 days you'll get a sum over the existing days, of which there will not be exactly 7. You can either ignore this or you can change the logic so that the result is calculated only if _7DayPeriod contains exactly 7 days.