Forum Discussion

EnochS's avatar
EnochS
Icon for Advocate II rankAdvocate 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

EnochS's avatar
EnochS
Icon for Advocate II rankAdvocate II
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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Advocate II rankAdvocate 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
      Icon for Helper I rankHelper 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