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
mficco
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

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
v-shex-msft
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.
amitchandak
Super User
Super User

@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))

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!
 

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.