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

ljerr, 

 

Thank you for helping me get this set up. This showed me how to setup the calendar and the measure for calculating on a time scale, which is the first part of the issue I was having. 

 

Could you also help me figure out how to create a rolling 5 week "average" as well? Where would I need to insert the "AVERAGE()" Formula, or would I need to divide the sum by 35 somewhere? I'll try to figure it out on my own with what you've given me but if you respond to how to do this, it would be greatly helpful as well.

 

Thanks again,

 

Enoch

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