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 measure like this: 

Amount-Week before = 
CALCULATE (
    SUM( covidpercounty[ConfirmedCovidCases] ),
    FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 && WEEKNUM(TODAY(),1)-1)
)

 

But this is giving me the current week value. It should give me the previous week value. So for week 11, the week before value should be 0 and week 12, it should be 3. 

I would then like to just subtract previous week from current week to calculate the total. 

 

Total per week = 
Var WeekBefore = 
CALCULATE (
    SUM( covidpercounty[ConfirmedCovidCases] ),
    FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 && WEEKNUM(TODAY(),1)-1)
)

Var ThisWeek =  
CALCULATE (
    SUM( covidpercounty[ConfirmedCovidCases] ),
    FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 )
)

Var Total = ThisWeek - WeekBefore

return

Var Total

 I've incuded the file here. Thanks for any help. 

  • 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.

5 Replies

    • MargaretJames's avatar
      MargaretJames
      Regular Visitor
      5 years ago

      amitchandak Thanks but I'm just taking the value of one day per week. This works fine. 

       

      Amount-Week before = 
CALCULATE (
    SUM( covidpercounty[ConfirmedCovidCases] ),
    FILTER ( 'Date', WEEKDAY ('Date'[Date] ) = 1 && WEEKNUM(TODAY(),1)-1)
)

      But I need to get the value for the previous week where Weekday is 1. Could you have a look at my file above please? 

       

      Thanks. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi MargaretJames 

         

        To have correct DAX formulas that work in all circumstances you have to follow certain rules. Deviate from them and you'll be in for a surprise. So, first off, to create correct time calculations you have to have a proper date table in the model with all time pieces that you'll need. Also, forget about WEEKNUM. This function does not work the way most people would expect it to (https://dax.guide/weeknum). 

         

        To correctly do what you want you have to create a proper date table where each day will be assigned to a unique week and each week will be 7 days long as it should. WEEKNUM does not guarantee this. Once you have a date table (call it Dates) with a column with unique week identifiers (call it Year-Week and give it the format YYYY-WKXX, where XX goes from 01 through to 53), you can now create the correct calculations.

         

        To see how such a setup should work (or a setup with some ISO Week calendars), please consult the articles at www.sqlbi.com. They have plenty of articles on how to correctly do week-based calculations. Please do not try to re-invent the wheel. It's tricky in DAX, to say the least, if you're not good at it. Saying from experience.