Forum Discussion
Rolling 6 Week Average
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)
Regards,
Xiaoxin Sheng
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.
That is rolling 42 days or 6 week.
Can you share sample data and sample output.
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...
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)
Regards,
Xiaoxin Sheng