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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MathLacome
Regular Visitor

Moving average based on Rolling 7 days - per customer/subject

Hi everyone, 

 

First, sorry for bothering you with an easy question but I can move forward in my model without this.

 

I have my data model where we can find my planning (Date, time, Id-session (which session has been done) and objectives.

I have a table where we can find, for each session, the exercise, objective, targeted group, sets, reps (...).

A table date where I have all the dates from the first day (a calendar...).

Finally, a table (Seances_Joueurs) where I have the player, the Id_session and the total number of reps, sets and TUT (sum) for the session.

 

Datamodel2.png

 

Now I would like to create in the Session_joueur table a calculate column to measure a rolling average over 7 days, for each players (Nom).

 

Datamodel.png

 

I tried with DATEINBETWEEN as well as this formula but the results are wrong...

 

 

Acute = calculate([NbRep totales]; ALL('Séances_Joueurs');FILTER(ALL('Séances_Joueurs'[Date]);'Séances_Joueurs'[Date]=EARLIER('Séances_Joueurs'[Date])-7))

 

 

Could one of you guys help me ?

 

All the best,

 

Mathieu.

1 ACCEPTED SOLUTION

@MathLacome

 

Please try with following measure for “Rolling sum”.

Rolling Sum 7 Days_Per Player = 
CALCULATE (
    SUM ( Table1[NbRep] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -7, DAY ),
    ALLEXCEPT ( Table1, Table1[Nom] )
)

Moving average based on Rolling 7 days - per customer subject_1.jpg

 

Best Regards,

Herbert

View solution in original post

8 REPLIES 8
v-haibl-msft
Employee
Employee

@MathLacome

 

In your dataset, it seems that each player does not have data for each day. I assume you want the moving average based on the existing 7 days in Seances_Joueurs table. (For PISCIONE, days are 7/7/2016, 7/9/2016, 7/12/2016 …)

 

First, create a column to rank the date for each player with following formula.

RankDate =
IF (
    Table1[Date] <> BLANK (),
    RANKX (
        FILTER (
            Table1,
            Table1[Nom] = EARLIER ( Table1[Nom] )
                && Table1[Date] <> BLANK ()
        ),
        Table1[Date],
        ,
        ASC,
        DENSE
    )
)

Moving average based on Rolling 7 days - per customer subject_1.jpg

 

Then create a measure to get the moving average with following formula.

Moving Average 7 Days = 
VAR SevenDaysTotal = 
CALCULATE (
    SUM ( Table1[NbRep] ),
    FILTER (
        ALL ( Table1[RankDate] ),
        Table1[RankDate] <= MAX ( Table1[RankDate] )
		&& Table1[RankDate] >= MAX( Table1[RankDate] ) - 6
    ),
    ALLEXCEPT ( Table1, Table1[Nom] )
)

VAR Days = CALCULATE (
    DISTINCTCOUNT ( Table1[RankDate] ),
    FILTER (
        ALL ( Table1[RankDate] ),
        Table1[RankDate] <= MAX ( Table1[RankDate] )
            && Table1[RankDate]
                >= MAX ( Table1[RankDate] ) - 6
            && Table1[RankDate] <> BLANK ()
    ),
    ALLEXCEPT ( Table1, Table1[Nom] )
)

RETURN
(
	DIVIDE( SevenDaysTotal, Days )
)

Moving average based on Rolling 7 days - per customer subject_2.jpg

 

Best Regards,

Herbert

Thanks everyone for your answers !

 

But... I'm still struggling !

 

Yes Herbert, I dont have "sessions" everyday but it is not a problem. I want 7 days on a rolling average.

So, I built a calendar table where I have everydates, id_day, id_month and so on for my visualisations.

 

I would like to : 

1. Create a measure "Rolling sum" or "Rolling average" to sum the number of reps, by players, on a 7 days rolling scale.

 

I want to be able to visualise it in a bar graph, day by day. So I need the measure to be calculate for today but also for today-1, today-2 .... to the end ! 😉

 

I tried your formula Herbert (but I only have 1 value !) but Its not working the way I want...

I also tried with this one : 

 

"

That [Units Sold] measure is the jagged red line in the chart at the top of the post, and its formula is very simple:

[Units Sold] = SUM(Sales[QtySold])

And we want a version of [Units Sold] that is “smoothed” over a 3-month period.

Moving Sum

Let’s start with a formula that is a sum of the most recent 3 months (including the current one):

[3 Month Moving Sum Units Sold] =
CALCULATE([Units Sold],
          DATESINPERIOD(Calendar[Date],
                        LASTDATE(Calendar[Date]),-3, Month

 

Found in PowerPivotPro but still not working...

 

@MathLacome

 

Please try with following measure for “Rolling sum”.

Rolling Sum 7 Days_Per Player = 
CALCULATE (
    SUM ( Table1[NbRep] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -7, DAY ),
    ALLEXCEPT ( Table1, Table1[Nom] )
)

Moving average based on Rolling 7 days - per customer subject_1.jpg

 

Best Regards,

Herbert

Thanks you guys,

 

All the above solutions where fully functionnal. My mistake was from making a bar chart with Date column and not id_Date Column.

My Date Column was returning aggregates of month and days in a month but not a ful calendar.

 

Thanks everyone for helping me to find the solution !

 

All the best,

 

Mathieu.

I have an equal problem, but instead of calculating the average i do the distinct count the last 14 days. In my data model I have created a Date table using "Date = CALENDARAUTO()" that I use as a slider for my "Line and Clustered Column Chart", and I'm using the Date in the Hierarchy format as the Shared Axis.

I'm using the formula below to calculate the distinct number the last 14 days:

Customers Last 14 days = CALCULATE(DISTINCTCOUNT(TransactionData[Customer]),DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-14,DAY))

  1. The DistinctCount for the current period (Day, Month, Quater, Year, depending on if I Drill Up of Down the diagram) I show as Bars
  2. The DistinctCount for the last 14 days I show as a Line
  3. I have also added a YearToDate measure that I show as a Second Line

 

 

I have two problems:

  • The calculations continues (and the value decreases) even after my current date (today it's the 6'th of September, so I have values untill the 19'th of September). This is easy to see when I list the values in a table
  • The last 14 days line does not show up for the last month when I Drilled to the Month based view in the diagram. The same goes for Quater and Year. Drilling down to the day view will show the "last 14 days line" up to todays date.
    Although, this is kind of strange, since I also have seen the "last 14 days line" streching out to the coming 13 days as I described as a problem above. Maybe Microsoft has fixed this in the later versions. But still, there's a problem that I don't see the "last 14 days line" extended into my current month. Using a YTD measure, this line do extend into the current month
kcantor
Community Champion
Community Champion

@MathLacome

Moving averages can be complex. Here is a blog that explains how to create them as well as correct them for the month level where the total days vary.

http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I already checked at the blog but there is still some point I do not understand.

I need to add another filter layout (players name) to have FOR EACH player/customer, the moving average.

 

Someone can help me with this ?

 

All the best.

 

Mathieu.

@MathLacome

 

Your sample data sccreen shows each player with multiple dates .  So how do you propose to show the output.

 

Is it going to be By Player, By Date and then the Moving Average over the last 7 days of the reported date ??

 

Please clarify, if possible attach the output format and result you expect based on the sample data.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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