Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EnochS
Helper II
Helper II

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. (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.

1 ACCEPTED SOLUTION

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

6 REPLIES 6
v-ljerr-msft
Employee
Employee

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

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

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

Hi @EnochS,

 

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

 

Regards

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.