Rolling 6 Week Average

[Anonymous]

I have a data set with date, agent names, task documents and hours. I calculate documents per hour by creating a measure Sum(Docs)/Sum(Hours). What I need is a 6 week average of docs per hour? So I need it to look back 6 weeks from the date and sum Docs and Hours so that I can plot both the Docs per Hour and the 6 week averge Docs per Hour on a line chart together. I also need this 6 week average to change dynamically with filters/slicers. For example, If I select both agents...it should be a combined 6 week average, or a specific task...it should only be a 6 week average for that task and that agent

Date	Agent Name	Task	Docs	Hours
3/2/2019	Agent A	BALANCE	147	43.8
3/2/2019	Agent B	SM-BANK-APPL	2	5.383333
4/6/2019	Agent A	BALANCE	115	38.166666
4/13/2019	Agent B	BALANCE	6	5.416666
3/9/2019	Agent B	SM-BANK-APPL	2	10.933333
5/4/2019	Agent A	BALANCE	123	41.583333
5/18/2019	Agent A	BALANCE	75	47.533333
5/11/2019	Agent B	BALANCE	5	5.25
4/27/2019	Agent A	BALANCE	98	45.1

[Anonymous · Accepted solution]

HI @Anonymous,

You can create a calculated column to calculate the 'rolling' average based on current date and agent, but it not able to be dynamic changes based on filter/slicer. Please use measure formula to instead, it can interact and respond with filter/slicers.

Time Intelligence "The Hard Way" (TITHW)  

dax WEEKNUM 

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Thanks everyone. I tried the solutions suggested, but none of them got me quite what I needed. What eneded up working was actually pretty simple. I had to create two measures. A rolling 6 Week Docs Total and a rolling 6 week Hours Total.

My calculation for Rolling 6 Week Docs Total:

Doc6Wk = CALCULATE(sum('Sample'[Docs]),

DATESINPERIOD('Sample'[Date],

LASTDATE('Sample'[Date]),-42, DAY

),

ALL('Sample'[Agent ID])

)

The calculation for Rolling 6 Week Hours was the same except replace "Docs" with "Hours". I added the "ALL" statement because I ended up plotting a line graph of the agent's Docs per Hour vs the entire group's Docs per Hour. Then I used both measures in a new measure.

Group Rolling 6 Week Docs Per Hour calculation:

Group Docs Per Hour = CALCULATE(

DIVIDE([Doc6Wk],[Hrs6Wk]),

ALL('Sample'[Agent ID]))

It works perfectly. Thanks again for all of the help, it ended up leading me to what I needed.

[amitchandak]

Check this file. It has 12 weeks rolling using a calendar. Please check this diff would

Last 6 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Anonymous]

I tried using the calculation above with a few tweaks to get the 6 week sum of the docs. It didn't seem to give me the correct amounts.

Six Week Docs = CALCULATE(

SUM('Sample'[Docs]),

FILTER(ALL('Sample'[date]),

'Sample'[date] >= DATEADD('Sample'[date],-42,DAY) && 'Sample'[date] <= 'Sample'[date]

))

[amitchandak]

That is rolling 42 days or 6 week.

Can you share sample data and sample output.

[Anonymous]

Here is the original data set.... (let me know if you want me to just send you the Excel file/pbix file, just send me your email)

Here is the desired output...I only really need the 6 week avg column...

[Anonymous · Accepted solution]

HI @Anonymous,

You can create a calculated column to calculate the 'rolling' average based on current date and agent, but it not able to be dynamic changes based on filter/slicer. Please use measure formula to instead, it can interact and respond with filter/slicers.

Time Intelligence "The Hard Way" (TITHW)  

dax WEEKNUM 

Regards,

Xiaoxin Sheng