Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling 6 Week Average

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 n...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      amitchandak
      Super User
      6 years ago

      That is rolling 42 days or 6 week.

      Can you share sample data and sample output.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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