Sum sessions between two dates

[Anonymous]

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. 

Data comes from csv exports from GA4, there are a lot of tables in the report that link to a date table [dim_date] via the date of the table. Tables are not linked together. 

I export the data on a Monday for the previous week which is a Mon-Sun scenario. This data is a few weeks old so the scenario is that I exported on 27/11/23 so the previous week dates are 20/11/23 to 26/11/23. 

I have a column for sessions that link to a landing page and would like to have last week and then the previous 8 weeks averaged. The dates for the 8 weeks are 25/09/23 to 19/11/23. 

Onto the formulas.

There is a column that worked however a circular dependency was found once I tried to add the 8w sessions:

Sessions_LW =

var _start = MAX(Landingpagemarketing[Date])-6

var _end = MAX(Landingpagemarketing[Date])

RETURN

CALCULATE(SUM(Landingpagemarketing[Sessions]),

Landingpagemarketing[Date]>= _start,Landingpagemarketing[Date]<=_end)

The other formulas I've tried: 

measures:

Date_8w_so = MAX(Landingpagemarketing[Date])-62

Date_8w_eo = MAX(Landingpagemarketing[Date])-7

Date_lw_so = MAX(Landingpagemarketing[Date])-6

Date_lw_eo = MAX(Landingpagemarketing[Date])

Sessions_LW v2 =

CALCULATE(SUM(Landingpagemarketing[Sessions]),Landingpagemarketing[Date]

>=[Date_lw_so],Landingpagemarketing[Date]<=[Date_lw_eo])

Sessions_LW v3 =

CALCULATE(SUM(Landingpagemarketing[Sessions]),

DATESBETWEEN(Landingpagemarketing[Date],[Date_lw_so],MAX(Landingpagemarketing[Date])))

Sessions_lW v4 =

var _start = [Date_lw_so]

var _end = [Date_lw_eo]

RETURN

CALCULATE(SUM(Landingpagemarketing[Sessions]),

Landingpagemarketing[Date]>= _start,Landingpagemarketing[Date]<=_end)

Another column I've tried: 

Sessions_8W =

var _start = MAX(Landingpagemarketing[Date])-60

var _end = MAX(Landingpagemarketing[Date])-6

RETURN

CALCULATE(SUM(Landingpagemarketing[Sessions]),

Landingpagemarketing[Date]>= _start,Landingpagemarketing[Date]<=_end)

The issue I'm having is that dates before the start of the 8 weeks are being included & switching < or > doesn't seem to work either. Please help before I drive myself insane 🙂

[Anonymous · Accepted solution]

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]

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]

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]

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.