Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Sum sessions between two dates

Hello, I've tried a few things & scoured the boards but still can't find an answer.    The annoying thing is, I've used this formula successfully in the past but can't seem to get it to work now.  ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    I managed to find the answer here: DAX Tables – Calendar and Time – Ville Gullstrands blogg (villezekeviking.com)

     

    Using the following measures for last week, the week before & previous 8 weeks:

     
    Sessions_LW = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-1) 
    Sessions_2W = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)
     
    Sessions_8W =
    var _wk1 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-9)
    var _wk2 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)
    var _wk3 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-3)
    var _wk4 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-4)
    var _wk5 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-5)
    var _wk6 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-6)
    var _wk7 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-7)
    var _wk8 = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-8)

    return  (_wk1 + _wk2 + _wk3 +_wk4 +_wk5 + _wk6 + _wk7 + _wk8)

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
2 years ago

Hello Anonymous,

 

Can you please try this:

 

  • Sessions for Last Week (Sessions_LW)

 

Sessions_LW =
VAR _end = MAX(dim_date[Date])
VAR _start = _end - 6
RETURN
CALCULATE(
    SUM(Landingpagemarketing[Sessions]),
    FILTER(
        Landingpagemarketing,
        Landingpagemarketing[Date] >= _start && Landingpagemarketing[Date] <= _end
    )
)

 

  • Sessions for the Previous 8 Weeks (Sessions_8W)

 

Sessions_8W =
VAR _end = MAX(dim_date[Date]) - 7
VAR _start = _end - 55  // 8 weeks minus 1 day, as _end is inclusive
RETURN
CALCULATE(
    SUM(Landingpagemarketing[Sessions]),
    FILTER(
        Landingpagemarketing,
        Landingpagemarketing[Date] >= _start && Landingpagemarketing[Date] <= _end
    )
)

 

  • Average for the Previous 8 Weeks

 

Average_Sessions_8W = DIVIDE([Sessions_8W],8)

 

Should you require any further assistance, please do not hesitate to reach out to me.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Sahir_Maharaj , I appreciate your suggestion.

 

I've tried the last week function & I'm returning dates in August which is definitely before last week. 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Sahir_Maharaj 

     

     

     

    I'm still having issues so any help would be appreciated. The dim_date table goes to the end of 2024 which is why (I'm guessing) the above is blank.