Forum Discussion

Dwalden's avatar
Dwalden
Regular Visitor
2 years ago
Solved

Creating a 4 Week Average

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Dwalden
Regular Visitor
2 years ago

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's avatar
Dwalden
Regular Visitor
2 years ago



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