Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

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:

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 🙂
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

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. 

 

goodbecenergy_0-1702476462131.png

 

goodbecenergy_1-1702476541955.png

 

 

 

Anonymous
Not applicable

Hi @Sahir_Maharaj 

 

goodbecenergy_0-1705070351509.png

 

goodbecenergy_1-1705070376824.png

 

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. 

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.