Creating a 4 Week Average

[Dwalden]

Hello everyone!

I am new to the PowerBi community. Ultimately, I have a table where I am counting the total records and want to average this out over the past 4 weeks. For example, over the past 4 weeks, what is the average number of records? 

To build on this, I would love to have a measure that looks at this same time period but at the previous year. I am trying to create a table that shows this years last 4 weeks compared to last year and shows if we are doing better or worse. 

Any feedback on how to accomplish this would be fantastic! 

[Anonymous · Accepted solution]

Hi @Dwalden 

Based on your needs, I have created the following table.

You can use the following dax to get the weekly average for the last four weeks and the weekly average for the same period last year.

Average Last 4 Weeks = 
CALCULATE (
    SUM ( 'Table'[Column1] ),
    FILTER (
        'Table',
        'Table'[date]
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 28 )
    )
) / 4

Average Last 4 Weeks Last Year = 
CALCULATE (
    SUM ( 'Table'[Column1] ),
    FILTER (
        'Table',
        'Table'[date]
            >= DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) - 28 )
            && 'Table'[date]
                < DATE ( YEAR ( TODAY () - 1 ), MONTH ( TODAY () ), DAY ( TODAY () ) )
    )
) / 4

You can use the following dax to determine if you are doing better or worse.

Measure = IF([Average Last 4 Weeks]>=[Average Last 4 Weeks Last Year],"better","worse")

This is the result you want.

Best Regards,

Jayleny

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

[Dwalden]

This is great! I think I have a new issue, or use case. I am wanting to use a date slicer to pull in the correct row counts. Example, I have one table of data and a new date table I created. Based on what my slicer is set, I would like to have the row count in one measure and then have the comparison dates from last year in the second measure. So, if I set my slicer to look at the last 4 weeks, I would love it to count these and then in another measure count the same period last year as those dates. Is there a way to accomplish this? Thanks so much!

[Dwalden]

Above, I want a measure next the 777 look at the same period referencing the slicer so I can make a dynamic difference calculation