Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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).
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.
Solved! Go to Solution.
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] ) )
Best Regards,
Herbert
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 ) )
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 ) )
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.
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...
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] ) )
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))
I have two problems:
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/
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.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |