Forum Discussion

EnochS's avatar
EnochS
Advocate II
9 years ago
Solved

Rolling Average by 5 Weeks (Weekly Report)

Hello,   I have spent 2 days trying to follow what some of the other posts on similar datasets have suggested as a solution but i'm having a hard time grasping how to do replicate it for my dataset...
Need Help
  • EnochS's avatar
    EnochS
    9 years ago

    Credit tov-ljerr-msft


    Here is what I  tried to do:

     

    5week avg = calculate( 

    Average(    Jobs_Invoices_Merged[Job Value]),

    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        -35,
        DAY
    )
)

     

    Updated Exported Data: Actual Data Updated.xlsx

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi EnochS,

 

The formula you provided above is right. It should work to calculate the rolling 5 week "average".:smileyhappy:

 

Regards

EnochS's avatar
EnochS
Advocate II
9 years ago

Thank you v-ljerr-msft! That works! The data looked wrong because it was calculating the average based on days instead of weeks, so to solve this I needed to create a seperate table based on weeks and aggregate the data so show the sum for that week. Im just going to adjust the formula to match the new table but the main structure is the same.

  • Twilla's avatar
    Twilla
    Helper I
    9 years ago
    5week avg = calculate( 

    Average(    Jobs_Invoices_Merged[Job Value]),

    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        -35,
        DAY
    )
)

     Hi EnochS,

     

    An easier way to get immediatly the weekly average is to write:

     

    Sum(Jobs_Invoices_Merged[Job Value])/5

     Instead of 

    Average(Jobs_Invoices_Merged[Job Value])

     Like this you don't have to start adding seperate tables