cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

mficco_1-1600221642548.png

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

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.

View solution in original post

5 REPLIES 5
Super User IV
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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?
 
 
 
 
 

pbi rank.PNG

 
 Thank you!
 
Community Support
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.

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.
 
 
 

Captura.PNG

 

 

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.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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