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

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

Hi EnochS,

 

According to your description above, I have made a sample with your shared data for your reference.:smileyhappy:

 

1. Create an individual and continuous Calendar table.

Date = CALENDARAUTO()

2. Add Year and Week column to the new created Date table.

Year = YEAR('Date'[Date])
Week = WEEKNUM('Date'[Date])

3. Create a relationship between your "Jobs_Invoices_Merged" table and the "Date" table with the Jobs_Invoices_Merged[Job Created] column and Date[Date] column.

 

 

4. Use the formula below to create a measure to calculate "5 Week Moving Sum".

5 Week Moving Sum = 
CALCULATE (
    [JV Sum],
    DATESINPERIOD (
        'Date'[Date],
        LASTDATE ( 'Date'[Date] ),
        -35,
        DAY
    )
)

5. Show the measure with Year and Week column on the Table visual.

 

 

Here is the sample pbix file for your reference.

 

Regards

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.