cancel
Showing results for
Did you mean:
Frequent Visitor

## Daily average per "rolling week"

Hi!

I need to calculate the daily avarege of persons using the data of the week. The logic should be like this: the calculation starts TODAY() and calculates the average of the last 7 days (including today). This average should be the same for that week. Then it must calculate the same average for the previous week. The graph should look like this one.

I managed to do a rolling average with this formula

Media Tot Personas =

CALCULATE(AVERAGE(Fechas[Tot Personas]);
FILTER(ALL(Fechas);
Fechas[Fecha] >= MAX(Fechas[Fecha])-7 &&
Fechas[Fecha] < MAX(Fechas[Fecha]) &&
MAX(Fechas[Fecha])<=TODAY())
)

but I need the week average to be constant like the graph above.

By the way my data is all in the same table (date and total persons).

Hope I am being clear enough.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

HI @mficco,

Perhaps you can add a condition in the expression to set up a rolling based on date field values:

Media Tot Personas =
VAR currDate =
MAX ( Fechas[Fecha] )
VAR _weeknum =
WEEKNUM ( currDate )
RETURN
CALCULATE (
AVERAGE ( Fechas[Tot Personas] );
FILTER (
ALL ( Fechas );
IF (
_weeknum > 1;
WEEKNUM ( Fechas[Fecha] ) = _weeknum - 1
&& YEAR ( Fechas[Fecha] ) = YEAR ( currDate );
WEEKNUM ( Fechas[Fecha] )
= WEEKNUM ( DATE ( YEAR ( currDate ) - 1; 12; 31 ) )
&& YEAR ( Fechas[Fecha] )
= YEAR ( currDate ) - 1
)
&& Fechas[Fecha] <= currDate
&& currDate <= TODAY ()
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Super User IV

@mficco , if you need same Average for the week then you can not do rolling 7 days.

You need have date/week table with week rank column

COlumn

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

And formula like this. Plot this with week from week/date table
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Tolling 7 would be like this, but that will change on day to day basis - Rolling 3 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-3,Day))

Proud to be a Super User!

Frequent Visitor

Sorry @amitchandak , I think I am not following this solution.

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

what do you mean by "Week start date"? I ranked the dates using this formula:

Week Rank = RANKX(all(Fechas);Fechas[Fecha];;ASC;Dense)

but this is just ranking all the days in my Date table. Shouldn't the formula rank 1° for the first week, then 2° for the second... so that the measures you recommended work correctly?

Thank you!

Community Support

HI @mficco,

I think you need to extract the current weeknum and year as a condition to group calculate instead of the current date or it will dynamic rolling records based on the current date.

Media Tot Personas =
VAR currDate =
MAX ( Fechas[Fecha] )
VAR _weeknum =
WEEKNUM ( currDate )
RETURN
CALCULATE (
AVERAGE ( Fechas[Tot Personas] );
FILTER (
ALL ( Fechas );
IF (
_weeknum > 1;
WEEKNUM ( Fechas[Fecha] ) = _weeknum - 1
&& YEAR ( Fechas[Fecha] ) = YEAR ( currDate );
WEEKNUM ( Fechas[Fecha] )
= WEEKNUM ( DATE ( YEAR ( currDate ) - 1; 12; 31 ) )
&& YEAR ( Fechas[Fecha] )
= YEAR ( currDate ) - 1
)
&& currDate <= TODAY ()
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Hi @v-shex-msft!

That's the idea. I need the average to dynamically roll whenever a day passes by. WEEKNUM() uses the week number of the year but I don't want the calendar week average. I need the week to roll, always starting form TODAY().

The quick solution I came up with was to make 3 measures (in order to show at least the last 3 weeks):

Current week= CALCULATE(SUM(Fechas[Tot Personas]);DATESBETWEEN(Fechas[Fecha];TODAY()-7;TODAY()))/7
Current week -1 = CALCULATE(SUM(Fechas[Tot Personas]);DATESBETWEEN(Fechas[Fecha];TODAY()-14;TODAY()-8))/7
Current week -2= CALCULATE(SUM(Fechas[Tot Personas]);DATESBETWEEN(Fechas[Fecha];TODAY()-21;TODAY()-15))/7

But in this way I can't show them in a graph cause it just shows a constant line through all the dates.

Community Support

HI @mficco,

Perhaps you can add a condition in the expression to set up a rolling based on date field values:

Media Tot Personas =
VAR currDate =
MAX ( Fechas[Fecha] )
VAR _weeknum =
WEEKNUM ( currDate )
RETURN
CALCULATE (
AVERAGE ( Fechas[Tot Personas] );
FILTER (
ALL ( Fechas );
IF (
_weeknum > 1;
WEEKNUM ( Fechas[Fecha] ) = _weeknum - 1
&& YEAR ( Fechas[Fecha] ) = YEAR ( currDate );
WEEKNUM ( Fechas[Fecha] )
= WEEKNUM ( DATE ( YEAR ( currDate ) - 1; 12; 31 ) )
&& YEAR ( Fechas[Fecha] )
= YEAR ( currDate ) - 1
)
&& Fechas[Fecha] <= currDate
&& currDate <= TODAY ()
)
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors