4 Weeks Average based on Day of the Week

[atjt217]

Hello, Im a new Power BI user and i hope someone can help me on this. I need to get the 4 weeks average based on the recent day of the week. Im using a relative date filter showing 4 weeks of data as it needs to be a moving avg. Here is the sample: 

For example the most recent date is Tuesday, I want to sum all Tuesdays (315 + 337 + 576 +602) and divide it by 4. 

The total will be placed on a card as a part of a Dashboard. 

[v-alq-msft · Accepted solution]

Hi, @atjt217 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

Table:

You may create a measure as below.

Result = 
var yesterday = TODAY()-1
var _weeday = WEEKDAY(yesterday)
var _weeknum = WEEKNUM(yesterday)
return
CALCULATE(
    AVERAGE('Table'[AppCount]),
    FILTER(
        ALL('Table'),
        [Date]<=yesterday&&
        [Weeknum]>=_weeknum-3&&
        [Weeknum]<=_weeknum&&
        [WeekDay]=_weeday
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrankAT]

Hi @atjt217 

based on your sample data and your description I get the following solution:

Measure = 
VAR _Min = CALCULATE(MIN('Table'[Date]),ALL('Table'[Date]))
VAR _Max = CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
RETURN
    CALCULATE(
        AVERAGE('Table'[ApptCount]),
        FILTER(
            'Table',
            'Table'[Date] >= _Min && 
            'Table'[Date] <= _Max && 
            'Table'[Weekday] = WEEKDAY(TODAY(),2) // from 1 = Monday to 7 = Sunday
        )
    )

What you have to keep in mind:

• The function WEEKDAY() doesn't support your weekday numbers from 1 = Saturday to 7 = Friday.
• This solution may be a step closer to what you have in mind, but no less.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)