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

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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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