4 week running average based on the slicer for a specific employee

[o-man]

How do I create a chart that shows a 4 week average based on today's date and an employee that the end user selects from a slicer?

The data has scores for people's performance (0-10) by day but would like to show the average of those scores by week but based on the selection of the end user's choice of employee.  I'd like the end user to be able to select one or more employees to see each of their 4 week running average against each other to compare. 

I may want to run the 4 week average based on today through the same time 4 weeks ago or based on the end of last week through 4 weeks earlier.  How are each of those written?

Is there something I need to do with my data to set it up correctly for this?  Do I need to manually create a date table versus using the embedded on in Power BI?

Thanks in advance.

[ankitpatira]

@o-man Considering you have data as shown below,

1. Make duplicate of date column and extract month of it as shown below and rename column as Month.

2. Similary make another duplicate of date column and extract Week of Month and rename column as WeekofMonth.

3. Your dataset should look like below.

4. Next highlight WeekofMonth column, right click and click Group By. Group By dialog box should look like below,

5. Finally Close & Apply.

Now you can choose Month, WeekofMonth and People column as slicers on report and create a table of those columns along with AvgPerf column. 

[o-man]

Thank you @ankitpatira.  I really appreciate it.  I'll take a crack at it today and let you all know.

[v-sihou-msft]

@o-man

You can also do it with DAX query. If you have data as below. You can create several measures to get 4 week average based on today or 4 weeks ago based on the end of last week.

Measures need to be created:

Date4weeksAgo(Based on today) = TODAY() -27

TotalDays = DATEDIFF([Date4weeksAgo(Based on today)], TODAY(), DAY) + 1

4weeksSumPerf =

CALCULATE (

    SUM ( Performance[Performance] ),

    FILTER (

        Performance,

        AND (

            Performance[Date] >= [Date4weeksAgo(Based on today)],

            Performance[Date] <= TODAY()

        )

    )

)

4weeksAvePerf = [4weeksSumPerf] / [TotalDays]

For 4 weeks ago based on the end of last week (assume week begins on Sunday and ends on Saturday), you just need to create a new measure. Then replace “[Date4weeksAgo(Based on today)]” with “[Date4weeksAgo(based on the end of last week)]” in above Measure2 and 3.

Date4weeksAgo(based on the end of last week) = TODAY() - WEEKDAY(TODAY()) – 27

Regards,