Forum Discussion
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.
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:
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_2W = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)Sessions_LW = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-1)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)
4 Replies
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.
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.
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.
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_2W = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-2)Sessions_LW = CALCULATE(SUM([Sessions]), dim_date[week_offset]=-1)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)
