Reply
Regular Visitor
Posts: 19
Registered: ‎01-06-2017
Accepted Solution

Rolling Average by 5 Weeks (Weekly Report)

[ Edited ]

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. (I just bought a book and planning to take a class to grow in these skills fundimentaly, but until then I would greatly appreciate any help or direction).

 

I need to create a report that shows me the last 5 weeks average sales $ amount won for that week.

Example:

 

Week 1; Last 5 weeks average

Week 2: Last 5 weeks average

Week 3: Last 5 weeks average

 

I need this to be in a table so that I can manually add (or via calculation) what the goal is for that week.

 

Here is what i've tried so far:

JV Sum = SUM(Jobs_Invoices_Merged[Job Value])
5 Week Moving Sum = 
CALCULATE([JV Sum], 
          DATESINPERIOD(Jobs_Invoices_Merged[Job Created], 
                        LASTDATE(Jobs_Invoices_Merged[Job Created]),-35, DAY
                       )
         )

Exported Data: Actual Data.xlsx

 

Exported this from PBI to excel and stuck it on a OneDrive..

 

Source Table: Jobs_Invoices_Merged

Jobs_Invoices_Merged.JPG

 

 

 

 

 

 

 

 

 

 

Date Table: Sales_Avg_Date_Table

 

Sales_Avg_Date_Table.JPG

These tables are not yet linked because I'm not sure how to link them properly (might be where my issue is)..

 

 

Optional:

I also created a filter to show only days bettween "completed & scheduled" to be between 0-30 days age, but I'm assuming that i can add that into the "visual level filters".. If not, then I have this custom column to use as a calculation if needed:

Job Days Age Custom Column.JPG

 

Job Days Age Filter.JPG

 

 

These are my attempted results:

Results.JPG

 

 

I'm realizing that I have much more to learn since we've been getting into these visuals that require more complex nested dax formulas..

 

Thank you for any help in advance.


Accepted Solutions
Highlighted
Regular Visitor
Posts: 19
Registered: ‎01-06-2017

Re: Rolling Average by 5 Weeks (Weekly Report)

[ Edited ]

Credit to@v-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 ) )

Results 2.JPG

 

Updated Exported Data: Actual Data Updated.xlsx

View solution in original post


All Replies
Super Contributor
Posts: 1,366
Registered: ‎07-17-2016

Re: Rolling Average by 5 Weeks (Weekly Report)

Hi @EnochS,

 

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

 

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.

 

relationship1.PNG

 

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.

 

r2.PNG

 

Here is the sample pbix file for your reference.

 

Regards

Regular Visitor
Posts: 19
Registered: ‎01-06-2017

Re: Rolling Average by 5 Weeks (Weekly Report)

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

Highlighted
Regular Visitor
Posts: 19
Registered: ‎01-06-2017

Re: Rolling Average by 5 Weeks (Weekly Report)

[ Edited ]

Credit to@v-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 ) )

Results 2.JPG

 

Updated Exported Data: Actual Data Updated.xlsx

Super Contributor
Posts: 1,366
Registered: ‎07-17-2016

Re: Rolling Average by 5 Weeks (Weekly Report)

Hi @EnochS,

 

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

 

Regards

Regular Visitor
Posts: 19
Registered: ‎01-06-2017

Re: Rolling Average by 5 Weeks (Weekly Report)

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.