Sum of sessions between 2 dates

[Anonymous]

Hello, 

Please help.

I'm trying to add up the number of sessions during a few time periods, I'll give the dates ranges in relation to today if the date was Monday 8th January 2024.

Name	Dates	Formulas
Last week	1/01/2024 to 7/01/2024	Date_lpm_lw_so = MAX(Landingpagemarketing[End_of_week])-6 Date_lpm_lw_eo = MAX(Landingpagemarketing[End_of_week])
2 weeks	25/12/23 to 31/12/23	Date_lpm_2w_so = MAX(Landingpagemarketing[Start of Week])-7 Date_lpm_2w_eo = MAX(Landingpagemarketing[End_of_week])-7
Previous 8 weeks	6/11/23 to 31/12/23	Date_lpm_8w_so = MAX(Landingpagemarketing[End_of_week])-62 Date_lpm_8w_eo = MAX(Landingpagemarketing[End_of_week])-7

Within the table landingpagemarketing, I have the following measure which counts correctly in a card but brings in other pages from other weeks when in a graph: 

Sessions_LW =

CALCULATE(SUM(Landingpagemarketing[Sessions]),DATESBETWEEN(dim_date[Date],[Date_lpm_lw_so],[Date_lpm_lw_eo]))

Speaking internally, I have to move the measures onto my date table (dim_date) which I have done but the measures aren't populating the graph at all now. I've done a week_Start and a week_end for each date in dim_date as well as last week, 2week and 8 week start and end dates. I've tried using datedifference but it doesn't work either.

There is a week index that I'm trying to use but the sql needs to be updated as it isn't aligned with the week numbers. I've also tried going backwards using the isoweeknum but we have just started a new year and I can't find an solution for that either. 

Other formulas I've used:

Sessions_LW v1a = DATESBETWEEN(Landingpagemarketing[Date],[Date_lpm_lw_so],[Date_lpm_lw_eo])

Sessions_LW v2 =

CALCULATE(SUM(Landingpagemarketing[Sessions]),

FILTER(Landingpagemarketing,Landingpagemarketing[Start of Week] = [Date_lpm_lw_so]),

FILTER(Landingpagemarketing,Landingpagemarketing[End_of_week] = [Date_lpm_lw_eo]))

Sessions_LW v3 =

var _start = MAX(Landingpagemarketing[Start of Week])

var _end = MAX(Landingpagemarketing[End_of_week])

return CALCULATE(SUM(Landingpagemarketing[Sessions]),Landingpagemarketing[Date]>=_start,Landingpagemarketing[Date]<=_end)

Sessions_LW v4 =

var _start = MAX(Landingpagemarketing[Start of Week])

var _sum = SUM(Landingpagemarketing[Sessions])

RETURN

IF(AND(MAX(Landingpagemarketing[Start of Week])=[Date_lpm_lw_so],

MAX(Landingpagemarketing[End_of_week])=[Date_lpm_lw_eo]),SUM(Landingpagemarketing[Sessions]),0)

Sessions_LW v5 =

CALCULATE(SUM(Landingpagemarketing[Sessions]),DATESBETWEEN(dim_date[Date],dim_date[bs_lw_start],dim_date[bs_lw_end]))

Sessions_LW v6 =

var _start = MAX(dim_date[bs_lw_start])

var _sum = SUM(dim_date[bs_lw_end])

RETURN

IF(AND(MAX(Landingpagemarketing[Start of Week])=[Date_lpm_lw_so],

MAX(Landingpagemarketing[End_of_week])=[Date_lpm_lw_eo]),SUM(Landingpagemarketing[Sessions]),0)

[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)

[v-shex-msft]

HI @Anonymous,

Perhaps you can take a look at the following blog about dynamic segments if helps:

Dynamic segmentation – DAX Patterns

Regards,

Xiaoxin Sheng